Query editor
-- Story: 고래는 알고 있었다-- Dashboard position: Q3 — USDC 고래 이동 vs WETH 시장 거래량-- Description: 최근 180일 USDC 대규모 이동(상위 1%)과 Ethereum USDC/WETH DEX 거래량 병치-- Parameter: {{token_address}} e.g. 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 (USDC)-- Parameter: {{token_decimals}} e.g. 6-- Parameter: {{comparison_token_address}} e.g. 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 (WETH)WITH large_moves AS (SELECTDATE_TRUNC('day', evt_block_time) AS day,COUNT(*) AS whale_tx_count,SUM(CAST(value AS DOUBLE) / POWER(10, {{token_decimals}})) AS whale_volume_tokenFROM erc20_ethereum.evt_TransferWHERE contract_address = {{token_address}}AND CAST(value AS DOUBLE) / POWER(10, {{token_decimals}}) > (SELECT APPROX_PERCENTILE(CAST(value AS DOUBLE) / POWER(10, {{token_decimals}}), 0.99)FROM erc20_ethereum.evt_TransferWHERE contract_address = {{token_address}}AND evt_block_date >= CURRENT_DATE - INTERVAL '180' DAY)AND evt_block_date >= CURRENT_DATE - INTERVAL '180' DAYGROUP BY 1),dex_vol AS (SELECTDATE_TRUNC('day', block_time) AS day,SUM(CASEWHEN token_bought_address = {{token_address}} OR token_sold_address = {{token_address}}THEN COALESCE(amount_usd, 0)ELSE 0END) AS usdc_dex_volume_usd,SUM(CASEWHEN token_bought_address = {{comparison_token_address}} OR token_sold_address = {{comparison_token_address}}THEN COALESCE(amount_usd, 0)ELSE 0END) AS weth_dex_volume_usdFROM dex.tradesWHERE blockchain = 'ethereum'AND block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '180' DAY)AND block_time >= NOW() - INTERVAL '180' DAYAND (token_bought_address = {{token_address}}OR token_sold_address = {{token_address}}OR token_bought_address = {{comparison_token_address}}OR token_sold_address = {{comparison_token_address}})GROUP BY 1)SELECTCOALESCE(w.day, d.day) AS day,COALESCE(w.whale_tx_count, 0) AS whale_tx_count,COALESCE(w.whale_volume_token, 0) AS usdc_whale_volume,COALESCE(d.usdc_dex_volume_usd, 0) AS usdc_dex_volume_usd,COALESCE(d.weth_dex_volume_usd, 0) AS weth_dex_volume_usdFROM large_moves wFULL OUTER JOIN dex_vol d ON w.day = d.dayORDER BY 1