Query editor
WITH weeks AS (SELECTweek_start,DATE_ADD('day', -1, week_start) AS snapshot_date,LEAST(DATE_ADD('day', 6, week_start), CURRENT_DATE) AS week_endFROM UNNEST(SEQUENCE(DATE '2025-01-06', DATE_TRUNC('week', CURRENT_DATE), INTERVAL '7' DAY)) AS u(week_start)),ranked_week_holders AS (SELECTw.week_start,w.snapshot_date,b.address AS wallet,b.balance AS usdc_balance,ROW_NUMBER() OVER (PARTITION BY w.week_start ORDER BY b.balance DESC) AS holder_rankFROM weeks wJOIN tokens_ethereum.balances bON b.token_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND CAST(b.block_time AS DATE) = w.snapshot_dateWHERE b.token_standard = 'erc20'AND b.balance > 0),week_cohort AS (SELECT week_start, snapshot_date, wallet, usdc_balance, holder_rankFROM ranked_week_holdersWHERE holder_rank <= 200),transfer_flows AS (SELECTw.week_start,w.snapshot_date,CASEWHEN t.contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 THEN 'USDC'WHEN t.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 THEN 'WETH'END AS asset,SUM(CASEWHEN t.contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 THEN CAST(t.value AS DOUBLE) / POWER(10, 6)WHEN t.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 THEN CAST(t.value AS DOUBLE) / POWER(10, 18)END) AS net_flow,SUM(CASEWHEN t.contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 THEN CAST(t.value AS DOUBLE) / POWER(10, 6)WHEN t.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 THEN CAST(t.value AS DOUBLE) / POWER(10, 18)END) AS gross_flow,COUNT(*) AS transfer_rowsFROM weeks wJOIN week_cohort c ON w.week_start = c.week_startJOIN erc20_ethereum.evt_Transfer tON t.evt_block_date BETWEEN w.week_start AND w.week_endAND t."to" = c.walletAND t.contract_address IN (0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2)WHERE t.evt_block_date >= DATE '2025-01-06'GROUP BY 1, 2, 3UNION ALLSELECTw.week_start,