--Credit for => forked from mamad-5XN3k3 / Top platforms by swap volume over the last three months @ https://flipsidecrypto.xyz/mamad-5XN3k3/q/94Q5F5DzWG4Q/top-platforms-by-swap-volume-over-the-last-three-months with price_tab AS ( SELECT hour::date as date, avg(price)::string as price, 'MAIN' :: string AS pool_type, 'WETH' :: string AS token_symbol, '0x98a8345bb9d3dda9d808ca1c9142a28f6b0430e1' :: string AS token_contract, '0x3ce1aa52eeaf57a355939c09ac76f03f01988f13' :: string AS pool_contract_address FROM ethereum.price.ez_prices_hourly WHERE symbol = 'WETH' AND token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, avg(price)::string as price, 'STARGATE' :: string AS pool_type, 'stgWETH' :: string AS token_symbol, '0x55acee547df909cf844e32dd66ee55a6f81dc71b' :: string AS token_contract, '0xdaf03b6431b422dc00e679d1fbe0d7679788dfe1' :: string AS pool_contract_address FROM ethereum.price.ez_prices_hourly WHERE symbol = 'WETH' AND token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, avg(price)::string as price, 'MAIN' :: string AS pool_type, 'WKLAY' :: string AS token_symbol, '0x19aac5f612f524b754ca7e7c41cbfa2e981a4432' :: string AS token_contract, '0x8a5562d2a17488647381c1a0e9cf527a01b6f3bd' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, avg(price)::string as price, 'LSD' :: string AS pool_type, 'WKLAY' :: string AS token_symbol, '0x19aac5f612f524b754ca7e7c41cbfa2e981a4432' :: string AS token_contract, '0x92e9850c72c351d6f1fbd18d8913546648316f77' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, avg(price)::string as price, 'STARGATE' :: string AS pool_type, 'WKLAY' :: string AS token_symbol, '0x19aac5f612f524b754ca7e7c41cbfa2e981a4432' :: string AS token_contract, '0x292867fc91836c0e8f62626ebd026b4a2ed32949' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, avg(price)::string as price, 'LSD' :: string AS pool_type, 'wGCKLAY' :: string AS token_symbol, '0xa9999999c3d05fb75ce7230e0d22f5625527d583' :: string AS token_contract, '0x9b58b9d82b47b9e9fdf76460b8e65df154664cfe' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, '1'::string as price, 'STABLES' :: string AS pool_type, 'USDC' :: string AS token_symbol, '0x608792deb376cce1c9fa4d0e6b7b44f507cffa6a' :: string AS token_contract, '0x2948f932a2a841b91cf74114f5e58b454201e914' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, avg(price)::string as price, 'LSD' :: string AS pool_type, 'stKAIA' :: string AS token_symbol, '0x42952b873ed6f7f0a7e4992e2a9818e3a9001995' :: string AS token_contract, '0xb10528e8754f01b77b63ee2fe66e9437881fc410' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, avg(price)::string as price, 'LSD' :: string AS pool_type, 'sKLAY' :: string AS token_symbol, '0xa323d7386b671e8799dca3582d6658fdcdcd940a' :: string AS token_contract, '0xd33db6dcfcbb57f604e6c731c6e278d36fa6912a' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, '1'::string as price, 'MAIN' :: string AS pool_type, 'USDT' :: string AS token_symbol, '0x5c13e303a62fc5dedf5b52d66873f2e59fedadc2' :: string AS token_contract, '0xa856b5da8e85b23395c17783954edda010317fce' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, '1'::string as price, 'STABLES' :: string AS pool_type, 'USDT' :: string AS token_symbol, '0x5c13e303a62fc5dedf5b52d66873f2e59fedadc2' :: string AS token_contract, '0xab4fc22974eacc7f3a18fa89fef5a5f9030275d7' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 UNION all SELECT hour::date as date, '1'::string as price, 'STARGATE' :: string AS pool_type, 'stgUSDT' :: string AS token_symbol, '0x9025095263d1e548dc890a7589a4c78038ac40ab' :: string AS token_contract, '0xb399961ceb3dc946d0cbfb373668b78cd6985e21' :: string AS pool_contract_address FROM kaia.price.ez_prices_hourly WHERE symbol = 'KLAY' AND hour::date >= current_date - interval '365 day' GROUP BY 1 ), data_tab AS ( SELECT log.value:SYMBOL::string AS symbol ,log.value:TOKEN_ADDRESS::string AS token_address ,log.value:DECIMAL::string AS decimal FROM (SELECT livequery.live.udf_api( 'https://flipsidecrypto.xyz/api/v1/queries/9cedd64b-79e6-4e93-882f-475718345fb5/data/latest') as resp ) ,LATERAL FLATTEN (input => resp:data) log ), allswaps_tab AS ( SELECT * ,regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data FROM kaia.core.fact_event_logs WHERE topics[0]::string = '0x7fa01e8d24e5a6ec56e00b4ff8ee7ed97e7650a7846ec494bbaa5d65f1be9ea4' ), raw_swap_tab AS ( SELECT block_timestamp ,tx_hash ,ORIGIN_FROM_ADDRESS ,origin_to_address ,contract_address ,CONCAT('0x', SUBSTR(topics [1] :: STRING, 10+17, 40)) AS sender ,CONCAT('0x', SUBSTR(topics [2] :: STRING, 10+17, 40)) AS to_address ,CONCAT('0x', SUBSTR(segmented_data [0] :: STRING, 10+15, 40)) AS fromToken ,CONCAT('0x', SUBSTR(segmented_data [1] :: STRING, 10+15, 40)) AS toToken ,TRY_TO_NUMBER(ethereum.public.udf_hex_to_int(segmented_data [2] :: STRING)):: STRING AS fromAmount ,TRY_TO_NUMBER(ethereum.public.udf_hex_to_int(segmented_data [3] :: STRING)):: STRING AS toAmount ,TRY_TO_NUMBER(ethereum.public.udf_hex_to_int(segmented_data [4] :: STRING)):: STRING AS toTokenFee FROM allswaps_tab WHERE tx_succeeded = TRUE ), tbl as ( SELECT DISTINCT tx_hash as tx_hash ,ORIGIN_FROM_ADDRESS as trader ,block_timestamp ,b.symbol AS symbol_in ,c.symbol AS symbol_out ,symbol_in|| ' => '|| symbol_out AS swap_route --,b.lp_contract ,fromToken AS token0_contract ,toToken AS token1_contract ,b.decimal AS decimal0 ,c.decimal AS decimal1 ,fromAmount AS token0_raw_amount ,toAmount as token1_raw_amount ,token0_raw_amount/pow(10,b.decimal) AS token0_amount ,token1_raw_amount/pow(10,c.decimal) AS token1_amount ,toTokenFee/pow(10,c.decimal) AS token1_fee_amount FROM raw_swap_tab a JOIN data_tab b on a.fromToken ::string = b.token_address ::string JOIN data_tab c on a.toToken ::string = c.token_address ::string WHERE block_timestamp::date >= '2024-05-01' -- WHERE block_timestamp::date >= current_date - 181 -- AND block_timestamp::date < current_date -- WHERE block_timestamp::date between (current_date - 31) and (current_date - 1) ), swaps_tab AS ( SELECT DISTINCT tx_hash AS tx_hash, block_timestamp::date AS day, trader, swap_route, concat('LP-' , symbol_out)::string AS pool_name, token0_amount ::string * b.price ::string AS token0_amount_usd, token1_amount ::string * c.price ::string AS swap_volume, token1_fee_amount ::string * c.price ::string AS swap_fee_usd FROM tbl a LEFT JOIN price_tab b on date_trunc('day' , a.block_timestamp) = b.date AND a.token0_contract = b.token_contract left JOIN price_tab c on date_trunc('day' , a.block_timestamp) = c.date AND a.token1_contract = c.token_contract WHERE tx_hash <> '0xadd1360e384f95028c0a11a121510809ad8fa641e16c0a3bf725eb45186edd8e' -- we have bug here AND swap_volume > 0 --AND (a.symbol_in = b.token_contract OR a.symbol_out = b.token_contract) --qualify row_number()over (partition BY tx_hash ORDER BY day) = 1 ), -- select -- swap_route, -- count (DISTINCT tx_hash) as transactions, -- count ( DISTINCT trader) as swappers, -- sum (swap_volume) as volume, -- from swaps_tab -- group by 1 -- ORDER by 3 desc final as ( select trader, sum(swap_volume) as volume from swaps_tab group by 1) select count(DISTINCT trader) as swappers, case when volume <= 10 then '<= $10 ' when volume <= 100 then '$10-$100' when volume <= 500 then '$100-$500 ' when volume <= 1000 then '$500-$1k ' when volume <= 10000 then '$1k-$10k' when volume <= 100000 then '$10k-$100k' when volume <= 500000 then '$100k-$500k' when volume <= 1000000 then '500k-1M' when volume > 1000000 then '> 1m' end as category from final where volume is not null group by 2 order by 1 desc

Overview of Capybara DEX