-- The same withdrawal concentration measure as 13_withdrawal_size_week.sql, one week earlier, so one week is not the whole story.
WITH p AS (
  SELECT * FROM UNNEST([{{TOKEN_PRICE_STRUCTS}}])
), w AS (
  SELECT event_memo AS dest, ABS(SAFE_CAST(delta_amount AS FLOAT64)) / POW(10, p.decimals) * p.price AS usd
  FROM `bigquery-public-data.crypto_near_mainnet_us.ft_events` e JOIN p ON e.contract_account_id = p.token
  WHERE block_date BETWEEN '2026-10-12' AND '2026-10-18' AND affected_account_id = 'intents.near'
    AND cause = 'ft_burn' AND event_memo LIKE 'WITHDRAW_TO:%'
    AND contract_account_id IN ({{INTENTS_TOKENS}})
), each_w AS (
  SELECT CASE WHEN usd < 100 THEN 'a <$100' WHEN usd < 1000 THEN 'b $100-1k' WHEN usd < 10000 THEN 'c $1k-10k'
              WHEN usd < 100000 THEN 'd $10k-100k' ELSE 'e >$100k' END AS bucket, usd
  FROM w
), d AS (
  SELECT dest, SUM(usd) AS usd, COUNT(*) AS n FROM w GROUP BY dest
), r AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY usd DESC) AS rk FROM d
)
SELECT 'bucket' AS kind, bucket AS k, COUNT(*) AS n, SUM(usd) AS usd FROM each_w GROUP BY bucket
UNION ALL SELECT 'top', 'top1', 1, SUM(IF(rk <= 1, usd, 0)) FROM r
UNION ALL SELECT 'top', 'top10', 10, SUM(IF(rk <= 10, usd, 0)) FROM r
UNION ALL SELECT 'top', 'top100', 100, SUM(IF(rk <= 100, usd, 0)) FROM r
UNION ALL SELECT 'top', 'top1000', 1000, SUM(IF(rk <= 1000, usd, 0)) FROM r
UNION ALL SELECT 'top', 'all', COUNT(*), SUM(usd) FROM r
ORDER BY 1, 2