Query editor
WITH fills AS (SELECT tx_hash, tx_to, amount_usd,CASEWHEN project = 'velodrome' THEN 'Velodrome'WHEN project = 'inkyswap' THEN 'InkySwap'WHEN project = 'uniswap' AND version = '3' THEN 'Uniswap v3'WHEN project = 'uniswap' THEN 'Uniswap (other)'WHEN project = 'reservoir_swap' THEN 'Reservoir'ELSE 'Other DEXes'END as venueFROM dex.tradesWHERE blockchain = 'ink'AND block_date >= DATE(CURRENT_DATE - INTERVAL '7' DAY)AND block_date <= CURRENT_DATEAND block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '7' DAY)AND (token_bought_address = 0x4200000000000000000000000000000000000006OR token_sold_address = 0x4200000000000000000000000000000000000006OR token_bought_address = 0x0000000000000000000000000000000000000000OR token_sold_address = 0x0000000000000000000000000000000000000000)),agg_tx AS (SELECT tx_hash,MIN(CASE projectWHEN '0x API' THEN '0x API'WHEN 'cow_protocol' THEN 'CoW Protocol'ELSE projectEND) as agg_nameFROM dex_aggregator.tradesWHERE blockchain = 'ink'AND block_date >= DATE(CURRENT_DATE - INTERVAL '7' DAY)AND block_date <= CURRENT_DATEAND block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '7' DAY)GROUP BY 1),attributed AS (SELECT COALESCE(a.agg_name,CASE f.tx_toWHEN 0x63951637d667f23d5251dedc0f9123d22d8595be THEN 'Direct: Velodrome router'WHEN 0xcaf22ce31298cf2bf1d152862f80216478ad7c67 THEN 'Retail router 0xcaf2...7c67'WHEN 0x1bcd304fdad1d1d66529159b1bc8d13c9158d586 THEN 'Retail router 0x1bcd...d586'ELSE 'Unlabeled routers / MEV bots'END) as source,f.venue, f.amount_usdFROM fills fLEFT JOIN agg_tx a ON a.tx_hash = f.tx_hash),totals AS (SELECT source, SUM(amount_usd) as src_total FROM attributed GROUP BY 1)SELECT at.source, at.venue, count(*) as n_trades,ROUND(SUM(at.amount_usd)) as volume_usd,ROUND(100.0 * SUM(at.amount_usd) / t.src_total, 1) as pct_of_sourceFROM attributed at JOIN totals t ON t.source = at.sourceGROUP BY 1, 2, t.src_totalORDER BY t.src_total DESC, volume_usd DESC