What does stablecoin supply by chain show?

with latest as (
  select date '2026-06-25' as as_of
), usd_universe as (
  select distinct symbol as token_symbol
  from stablecoins_multichain.tokens
  where currency = 'USD'
), by_chain_asset as (
  select
    l.as_of,
    b.blockchain as chain,
    b.token_symbol as asset,
    sum(b.balance_usd) as supply_usd
  from stablecoins_multichain.balances b
  join latest l on b.day = l.as_of
  join usd_universe u on b.token_symbol = u.token_symbol
  where b.balance > 0
  group by 1, 2, 3
), chain_totals as (
  select
    as_of,
    chain,
    sum(supply_usd) as supply_usd,
    count(*) as asset_count
  from by_chain_asset
  group by 1, 2
), market_totals as (
  select
    as_of,
    sum(supply_usd) as total_tracked_balances_usd,
    count(distinct chain) as denominator_chain_count,
    count(distinct asset) as denominator_asset_count
  from by_chain_asset
  group by 1
), ranked_chains as (
  select
    c.as_of,
    c.chain,
    c.supply_usd,
    c.asset_count,
    m.total_tracked_balances_usd,
    m.denominator_chain_count,
    m.denominator_asset_count,
    row_number() over (partition by c.as_of order by c.supply_usd desc, c.chain asc) as rank
  from chain_totals c
  join market_totals m on c.as_of = m.as_of
), labeled_assets as (
  select
    a.as_of,
    case when r.rank <= 6 then a.chain else 'Other chains' end as chain,
    case
      when a.asset in ('USDT', 'USDC', 'USDS', 'USDe', 'DAI', 'USD1') then a.asset
      else 'Other assets'
    end as asset_bucket,
    sum(a.supply_usd) as supply_usd,
    max(r.total_tracked_balances_usd) as total_tracked_balances_usd,
    max(r.denominator_chain_count) as denominator_chain_count,
    max(r.denominator_asset_count) as denominator_asset_count,
    min(case when r.rank <= 6 then r.rank else 999 end) as display_order,
    count(distinct a.chain) as chain_count_in_bucket,
    count(distinct a.asset) as asset_count_in_bucket
  from by_chain_asset a
  join ranked_chains r on a.as_of = r.as_of and a.chain = r.chain
  group by 1, 2, 3
), stack_rows as (
  select
    *,
    supply_usd / nullif(sum(supply_usd) over (partition by as_of, chain), 0) * 100 as chain_asset_share_percent,
    sum(supply_usd) over (partition by as_of, chain) / nullif(total_tracked_balances_usd, 0) * 100 as chain_market_share_percent
  from labeled_assets
), dominant_asset as (
  select
    as_of,
    chain,
    asset_bucket as dominant_asset,
    supply_usd as dominant_asset_supply_usd,
    chain_asset_share_percent as dominant_asset_share_percent
  from (
    select
      *,
      row_number() over (
        partition by as_of, chain
        order by supply_usd desc, asset_bucket asc
      ) as segment_rank
    from stack_rows
  )
  where segment_rank = 1
), final_rows as (
  select
    s.as_of,
    s.chain,
    s.asset_bucket as asset,
    s.asset_bucket,
    s.supply_usd,
    s.chain_asset_share_percent,
    s.chain_market_share_percent,
    s.total_tracked_balances_usd,
    s.denominator_chain_count,
    s.denominator_asset_count,
    s.display_order,
    s.chain_count_in_bucket,
    s.asset_count_in_bucket,
    d.dominant_asset,
    d.dominant_asset_supply_usd,
    d.dominant_asset_share_percent,
    concat(
      d.dominant_asset,
      ' at ',
      format('%.1f', d.dominant_asset_share_percent),
      '% of chain balances'
    ) as dominant_asset_context
  from stack_rows s
  join dominant_asset d on s.as_of = d.as_of and s.chain = d.chain
)
select
  as_of,
  chain,
  asset,
  asset_bucket,
  supply_usd,
  chain_asset_share_percent,
  chain_market_share_percent,
  total_tracked_balances_usd,
  denominator_chain_count,
  denominator_asset_count,
  display_order,
  chain_count_in_bucket,
  asset_count_in_bucket,
  dominant_asset,
  dominant_asset_supply_usd,
  dominant_asset_share_percent,
  dominant_asset_context
from final_rows
order by
  display_order,
  case asset_bucket
    when 'USDT' then 1
    when 'USDC' then 2
    when 'USDS' then 3
    when 'USDe' then 4
    when 'DAI' then 5
    when 'USD1' then 6
    else 99
  end,
  supply_usd desc