-- Distinct external withdrawal destinations in the first seven days of each month, and how concentrated they are.
-- Sampled to one week a month because event_memo is costly to scan.
WITH w AS (
  SELECT DATE_TRUNC(block_date, MONTH) AS m, contract_account_id AS token, event_memo AS dest,
         ABS(SAFE_CAST(delta_amount AS FLOAT64)) AS amt
  FROM `bigquery-public-data.crypto_near_mainnet_us.ft_events`
  WHERE (block_date BETWEEN '2025-07-01' AND '2025-07-07' OR block_date BETWEEN '2025-08-01' AND '2025-08-07' OR block_date BETWEEN '2025-09-01' AND '2025-09-07' OR block_date BETWEEN '2025-10-01' AND '2025-10-07' OR block_date BETWEEN '2025-11-01' AND '2025-11-07' OR block_date BETWEEN '2025-12-01' AND '2025-12-07' OR block_date BETWEEN '2026-01-01' AND '2026-01-07' OR block_date BETWEEN '2026-02-01' AND '2026-02-07' OR block_date BETWEEN '2026-03-01' AND '2026-03-07' OR block_date BETWEEN '2026-04-01' AND '2026-04-07' OR block_date BETWEEN '2026-05-01' AND '2026-05-07' OR block_date BETWEEN '2026-06-01' AND '2026-06-07' OR block_date BETWEEN '2026-07-01' AND '2026-07-07' OR block_date BETWEEN '2026-08-01' AND '2026-08-07' OR block_date BETWEEN '2026-09-01' AND '2026-09-07' OR block_date BETWEEN '2026-10-01' AND '2026-10-07')
    AND contract_account_id IN ({{INTENTS_TOKENS}}) AND affected_account_id = 'intents.near' AND cause = 'ft_burn'
    AND event_memo LIKE 'WITHDRAW_TO:%'
), d AS (
  SELECT m, dest, COUNT(*) AS n FROM w GROUP BY 1, 2
), r AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY m ORDER BY n DESC) AS rk FROM d
)
SELECT m, COUNT(*) AS destinations, SUM(n) AS withdrawals,
       SUM(IF(rk <= 1, n, 0)) AS top1, SUM(IF(rk <= 10, n, 0)) AS top10, SUM(IF(rk <= 100, n, 0)) AS top100,
       COUNTIF(n >= 2) AS repeat_destinations
FROM r GROUP BY 1 ORDER BY 1