Claude Code Usage via DuckDB

ccusage.sql

with raw_data as (
  select
    message.id::varchar as message_id,
    requestid::varchar as request_id,
    message.model as model,
    coalesce(message.usage.input_tokens, 0)::bigint as input_tokens,
    coalesce(message.usage.cache_creation_input_tokens, 0)::bigint as cache_creation,
    coalesce(message.usage.cache_read_input_tokens, 0)::bigint as cache_read,
    coalesce(message.usage.output_tokens, 0)::bigint as output_tokens,
    timestamp::timestamp as ts
  from read_json_auto(
    '~/.claude/projects/*/*.jsonl',
    format = 'newline_delimited',
    ignore_errors = true
  )
  where type = 'assistant'
    and message.usage is not null
    and message.model <> '<synthetic>'
),
with_ids as (
  select distinct on (message_id, request_id)
    *
  from raw_data
  where message_id is not null
    and request_id is not null
),
without_ids as (
  select
    *
  from raw_data
  where message_id is null
     or request_id is null
),
deduped as (
  select * from with_ids
  union all
  select * from without_ids
),
pricing as (
  select
    *
  from (values
    ('claude-sonnet-4-20250514',   3.0,  15.0,  3.75, 0.30),
    ('claude-sonnet-4-5-20250929', 3.0,  15.0,  3.75, 0.30),
    ('claude-opus-4-20250514',     15.0, 75.0, 18.75, 1.50),
    ('claude-opus-4-1-20250805',   15.0, 75.0, 18.75, 1.50),
    ('claude-opus-4-5-20251101',   5.0,  25.0,  6.25, 0.50),
    ('claude-haiku-4-5-20251001',  1.0,   5.0,  1.25, 0.10)
  ) as t(model, input_usd_m, output_usd_m, cache_create_usd_m, cache_read_usd_m)
),
costs as (
  select
    d.*,
    coalesce(
      (d.input_tokens   * p.input_usd_m        / 1e6) +
      (d.output_tokens  * p.output_usd_m       / 1e6) +
      (d.cache_creation * p.cache_create_usd_m / 1e6) +
      (d.cache_read     * p.cache_read_usd_m   / 1e6),
      0
    ) as cost_usd
  from deduped d
  left join pricing p
    on d.model = p.model
)
select
  ts::date as day,
  count(*) as requests,
  sum(input_tokens) as input,
  sum(cache_creation) as cache_create,
  sum(cache_read) as cache_read,
  sum(output_tokens) as output,
  round(sum(cost_usd), 2) as cost_usd
from costs
group by 1
order by 1 desc;