Which chains process the most gross stablecoin transfer volume?

-- Source compatibility fix: stablecoin_highway_transfer_activity.v1
-- Dune's legacy stablecoin transfer views remain catalogued but are no longer
-- executable. Use the public token transfer tables and constrain their rows to
-- Dune's official Stablecoin Atlas coverage list. The output grain, denominator,
-- weekly window, and logical metric contract remain unchanged.

WITH schedule_anchor AS (
  SELECT CAST(
    date_add(
      'day',
      -CAST(mod(day_of_week(current_date) + 1, 7) AS BIGINT),
      current_date
    ) AS date
  ) AS period_end
),
window_bounds AS (
  SELECT
    'week' AS window_role,
    CAST(date_add('day', -7, period_end) AS date) AS period_start,
    period_end
  FROM schedule_anchor
),
atlas_tokens AS (
  SELECT DISTINCT
    LOWER(TRIM(blockchain)) AS blockchain,
    LOWER(TRIM(token_address)) AS token_address,
    UPPER(TRIM(currency)) AS currency,
    TRIM(token_symbol) AS token_symbol
  FROM dune.dune_curated_data.result_stablecoin_atlas_coverage
  WHERE token_address IS NOT NULL
    AND token_symbol IS NOT NULL
    AND UPPER(TRIM(currency)) = 'USD'
),
normalized_transfers AS (
  SELECT
    LOWER(TRIM(t.blockchain)) AS blockchain,
    t.block_date,
    LOWER(CAST(t.contract_address AS varchar)) AS token_address,
    a.token_symbol,
    a.currency,
    t.amount,
    t.amount_usd
  FROM tokens.transfers t
  CROSS JOIN window_bounds w
  INNER JOIN atlas_tokens a
    ON a.blockchain = LOWER(TRIM(t.blockchain))
   AND a.token_address = LOWER(CAST(t.contract_address AS varchar))
  WHERE t.block_month BETWEEN
      CAST(date_trunc('month', w.period_start) AS date)
      AND CAST(date_trunc('month', w.period_end) AS date)
    AND t.block_date > w.period_start
    AND t.block_date <= w.period_end
    AND t.blockchain <> 'solana'

  UNION ALL

  SELECT
    'solana' AS blockchain,
    t.block_date,
    LOWER(TRIM(t.token_mint_address)) AS token_address,
    a.token_symbol,
    a.currency,
    t.amount_display AS amount,
    t.amount_usd
  FROM tokens_solana.transfers t
  CROSS JOIN window_bounds w
  INNER JOIN atlas_tokens a
    ON a.blockchain = 'solana'
   AND a.token_address = LOWER(TRIM(t.token_mint_address))
  WHERE t.block_date > w.period_start
    AND t.block_date <= w.period_end
),
valued_transfers AS (
  SELECT
    blockchain,
    block_date,
    token_address,
    token_symbol,
    currency,
    amount,
    CASE
      WHEN amount_usd IS NOT NULL AND amount_usd > 0 THEN amount_usd
      -- Polymarket documents this exact Polygon contract as a USDC-backed
      -- 1:1 claim. Keep the exception address-bound; never infer a peg from
      -- a token symbol alone.
      WHEN blockchain = 'polygon'
        AND token_address = '0xc011a7e12a19f7b1f670d46f03b03f3342e82dfb'
        THEN amount
      ELSE 0
    END AS effective_amount_usd,
    CASE
      WHEN amount_usd IS NOT NULL AND amount_usd > 0 THEN true
      WHEN blockchain = 'polygon'
        AND token_address = '0xc011a7e12a19f7b1f670d46f03b03f3342e82dfb'
        THEN true
      ELSE false
    END AS has_usd_valuation
  FROM normalized_transfers
)
SELECT
  w.window_role,
  w.period_start,
  w.period_end,
  t.blockchain AS chain,
  t.token_address,
  t.token_symbol AS asset_raw,
  t.currency,
  SUM(
    CASE
      WHEN t.has_usd_valuation THEN t.effective_amount_usd
      ELSE 0
    END
  ) AS gross_transfer_volume_usd,
  SUM(t.amount) AS gross_transfer_face_value_usd,
  SUM(
    CASE
      WHEN t.has_usd_valuation THEN t.amount
      ELSE 0
    END
  ) AS priced_transfer_face_value_usd,
  COUNT(*) AS transfer_event_count,
  COUNT_IF(t.has_usd_valuation)
    AS priced_transfer_event_count,
  'dune:tokens.transfers+tokens_solana.transfers;atlas_coverage' AS source
FROM valued_transfers t
CROSS JOIN window_bounds w
WHERE t.amount IS NOT NULL
  AND t.amount > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7
HAVING COUNT(*) > 0
ORDER BY chain, gross_transfer_volume_usd DESC, asset_raw, token_address;