Which chains gained or lost stablecoin supply share over the displayed week?

-- Source migration preview: stablecoin_highway_supply_endpoints.v1
-- Dune's former stablecoins_multichain balance tables are no longer
-- executable for this query owner. This preview uses Dune's own Stablecoin
-- Atlas daily supply result and anchors all comparisons to its latest
-- available observation. It creates no public binding or promotion.

WITH usd_stablecoin_universe AS (
  SELECT DISTINCT
    LOWER(TRIM(blockchain)) AS chain,
    TRIM(token_symbol) AS asset_raw
  FROM dune.dune_curated_data.result_stablecoin_atlas_coverage
  WHERE UPPER(TRIM(currency)) = 'USD'
    AND blockchain IS NOT NULL
    AND token_symbol IS NOT NULL
),
source_rows AS (
  SELECT
    CAST(s.day AS date) AS date,
    LOWER(TRIM(s.blockchain)) AS chain,
    TRIM(s.token_symbol) AS asset_raw,
    s.circulating_supply_usd AS supply_usd
  FROM dune.dune.result_stablecoin_atlas_daily_supply_by_blockchain s
  JOIN usd_stablecoin_universe u
    ON u.chain = LOWER(TRIM(s.blockchain))
    AND u.asset_raw = TRIM(s.token_symbol)
  WHERE s.day >= CAST(date_add('day', -45, current_date) AS date)
    AND s.day < current_date
    AND s.blockchain IS NOT NULL
    AND s.token_symbol IS NOT NULL
    AND s.circulating_supply_usd IS NOT NULL
    AND s.circulating_supply_usd > 0
),
date_coverage AS (
  SELECT
    date,
    COUNT(*) AS row_count,
    COUNT(DISTINCT chain) AS chain_count
  FROM source_rows
  GROUP BY 1
),
coverage_reference AS (
  SELECT
    MAX(row_count) AS max_row_count,
    MAX(chain_count) AS max_chain_count
  FROM date_coverage
),
latest_observation AS (
  SELECT MAX(c.date) AS latest_date
  FROM date_coverage c
  JOIN date_coverage w
    ON w.date = CAST(date_add('day', -7, c.date) AS date)
  JOIN date_coverage f
    ON f.date = CAST(date_add('day', -28, c.date) AS date)
  CROSS JOIN coverage_reference r
  WHERE c.row_count >= r.max_row_count * 0.9
    AND w.row_count >= r.max_row_count * 0.9
    AND f.row_count >= r.max_row_count * 0.9
    AND c.chain_count >= r.max_chain_count * 0.9
    AND w.chain_count >= r.max_chain_count * 0.9
    AND f.chain_count >= r.max_chain_count * 0.9
),
endpoint_dates (endpoint_role, endpoint_date) AS (
  SELECT 'current', latest_date
  FROM latest_observation
  UNION ALL
  SELECT 'week_ago', CAST(date_add('day', -7, latest_date) AS date)
  FROM latest_observation
  UNION ALL
  SELECT 'four_weeks_ago', CAST(date_add('day', -28, latest_date) AS date)
  FROM latest_observation
),
endpoint_supply AS (
  SELECT
    e.endpoint_role,
    s.date,
    s.chain,
    s.asset_raw,
    SUM(s.supply_usd) AS supply_usd
  FROM source_rows s
  JOIN endpoint_dates e
    ON s.date = e.endpoint_date
  GROUP BY 1, 2, 3, 4
)
SELECT
  endpoint_role,
  date,
  chain,
  asset_raw,
  supply_usd,
  SUM(supply_usd) OVER (PARTITION BY endpoint_role) AS endpoint_total_usd,
  'dune:stablecoin_atlas.daily_usd_supply_by_blockchain' AS source
FROM endpoint_supply
ORDER BY
  CASE endpoint_role
    WHEN 'current' THEN 1
    WHEN 'week_ago' THEN 2
    ELSE 3
  END,
  chain,
  supply_usd DESC;