Implementation:Helicone Helicone ClickhousePriceCalc

Knowledge Sources	Helicone
Domains	Cost Calculation, Analytics Database, SQL Generation
Last Updated	2026-02-14 00:00 GMT

Overview

Concrete function for generating a ClickHouse SQL CASE expression that computes aggregated LLM request costs server-side, provided by the @helicone/cost package.

Description

The clickhousePriceCalc function generates a SQL fragment containing a sum(CASE ... END) expression that computes the total cost of LLM requests directly in ClickHouse. It works by:

1. Iterating over all registered providers that have cost data (excluding the default OpenAI provider from the outer CASE, since it is used as the ELSE fallback).
2. For each provider, generating a WHEN (table.provider = 'PROVIDER_NAME') THEN (...) clause containing an inner CASE that matches model names against the provider's cost entries.
3. Each model match computes cost as a sum of: rate * table.column for each token type (prompt, completion, audio input/output, cache write, cache read) plus any fixed per-image and per-call fees.
4. The default (ELSE) branch uses the OpenAI cost table as fallback.
5. All per-token rates are pre-multiplied by COST_PRECISION_MULTIPLIER (1,000,000,000) and rounded to integers for fixed-point precision.
6. When inDollars is true (default), the entire sum is divided by the precision multiplier to return the result in USD.

The module also exports two companion functions:

• clickhousePriceCalcNonAggregated: Generates a non-aggregated CASE expression (no sum() wrapper) for per-row cost calculation, used for backfilling.
• clickhouseModelFilter: Generates a SQL WHERE clause to filter rows matching specific model/provider combinations.

Usage

Use this function when constructing ClickHouse queries that need to compute or aggregate costs. Embed the returned SQL fragment in a SELECT clause.

Code Reference

Source Location

• Repository: Helicone
• File: packages/cost/index.ts (lines 302-327)
• Helper: caseForCost function at lines 155-240 (same file)
• Constant: COST_PRECISION_MULTIPLIER = 1_000_000_000 in packages/cost/costCalc.ts (line 8)

Signature

export function clickhousePriceCalc(
  table: string,
  inDollars: boolean = true
): string

Import

import { clickhousePriceCalc } from "@helicone/cost";

I/O Contract

Inputs

Name	Type	Required	Description
table	string	Yes	The ClickHouse table name or alias to reference in the generated SQL (e.g., "request_response_rmt"). Token columns like prompt_tokens, completion_tokens, provider, and model must exist on this table.
inDollars	boolean	No	Whether to divide the result by the precision multiplier to return USD (default: true). Set to false to get the raw integer value for further computation.

Outputs

Name	Type	Description
return	string	A SQL fragment containing a sum(CASE ... END) / MULTIPLIER expression ready to be embedded in a ClickHouse SELECT clause.

Required Table Columns

Column	Type	Description
provider	String	The provider identifier (e.g., "OPENAI", "ANTHROPIC")
model	String	The model identifier (e.g., "gpt-4o", "claude-sonnet-4-20250514")
prompt_tokens	Int64	Number of prompt/input tokens
completion_tokens	Int64	Number of completion/output tokens
prompt_audio_tokens	Int64	Number of input audio tokens
completion_audio_tokens	Int64	Number of output audio tokens
prompt_cache_write_tokens	Int64	Number of cache write tokens
prompt_cache_read_tokens	Int64	Number of cache read tokens

Usage Examples

Basic Usage

import { clickhousePriceCalc } from "@helicone/cost";

const costExpr = clickhousePriceCalc("request_response_rmt");

// Use in a ClickHouse query
const query = `
  SELECT
    toDate(request_created_at) AS day,
    ${costExpr} AS total_cost
  FROM request_response_rmt
  WHERE organization_id = {orgId:String}
    AND request_created_at >= {startDate:DateTime}
  GROUP BY day
  ORDER BY day
`;

Raw Integer Mode (No Dollar Conversion)

// Get raw integer values (not divided by precision multiplier)
const rawCostExpr = clickhousePriceCalc("r", false);

const query = `
  SELECT
    model,
    ${rawCostExpr} AS cost_raw
  FROM request_response_rmt AS r
  GROUP BY model
`;
// cost_raw values are in units of 1/1,000,000,000 of a dollar

Generated SQL Structure

The function produces SQL with the following structure:

sum(
  CASE
    WHEN (t.provider = 'ANTHROPIC') THEN (
      CASE
        WHEN (t.model ILIKE 'claude-sonnet-4-20250514') THEN
          3000000 * t.prompt_tokens + 15000000 * t.completion_tokens + ...
        WHEN (t.model ILIKE 'claude-3-haiku-20240307') THEN
          250000 * t.prompt_tokens + 1250000 * t.completion_tokens + ...
        ELSE toInt64(0)
      END
    )
    WHEN (t.provider = 'GOOGLE') THEN (...)
    ...
    ELSE
      CASE
        WHEN (t.model ILIKE 'gpt-4o') THEN ...
        ...
        ELSE toInt64(0)
      END
  END
) / 1000000000

Related Pages

Implements Principle

• Principle:Helicone_Helicone_ClickHouse_Cost_SQL_Generation

Uses Heuristic

• Heuristic:Helicone_Helicone_Cost_Precision_Multiplier