with swaps as ( select block_timestamp::date as date, tx_hash, platform, swapper, token_in, case when token_in = '0x2ebb2ccac5e027a87fa0e2e5f656a3a4238d6a48d93ec9b610d570fc0aa0df12' then 'CELL' when token_in = '0x4def3d3dee27308886f0a3611dd161ce34f977a9a5de4e80b237225923492a2a::coin::T' then 'GARI' else SYMBOL_IN end as symb_in, amount_in, amount_in_usd, case when token_out = '0x2ebb2ccac5e027a87fa0e2e5f656a3a4238d6a48d93ec9b610d570fc0aa0df12' then 'CELL' when token_out = '0x4def3d3dee27308886f0a3611dd161ce34f977a9a5de4e80b237225923492a2a::coin::T' then 'GARI' else SYMBOL_OUT end as symb_out, token_out, amount_out, amount_out_usd from aptos.defi.ez_dex_swaps) , price as ( Select trunc(TO_TIMESTAMP(value[0]::string),'day') as day, '0x2ebb2ccac5e027a87fa0e2e5f656a3a4238d6a48d93ec9b610d570fc0aa0df12' as tok_address, avg(value[1]) as avg_price from ( SELECT livequery.live.udf_api( 'https://api.coingecko.com/api/v3/coins/cellena-finance/market_chart?vs_currency=usd&days=90') as resp ) ,LATERAL FLATTEN (input => resp:data:prices) group by all UNION select hour::date as day, '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' as tok_address, avg(price) as avg_price from aptos.price.ez_prices_hourly where symbol = 'APT' group by 1,2 UNION select hour::date as day, '0xd11107bdf0d6d7040c6c0bfbdecb6545191fdf13e8d8d259952f53e1713f61b5::staked_coin::StakedAptos' as tok_address, avg(price) as avg_price from aptos.price.ez_prices_hourly where symbol = 'APT' group by 1,2 UNION select hour::date as day, token_address as tok_address, avg(price) as avg_price from aptos.price.ez_prices_hourly where symbol = 'APT' group by 1,2 UNION select hour::date as day, '0x8d87a65ba30e09357fa2edea2c80dbac296e5dec2b18287113500b902942929d::celer_coin_manager::UsdcCoin' as tok_address, 1 as avg_price from aptos.price.ez_prices_hourly where symbol = 'APT' union select hour::date as day, '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC' as tok_address, 1 as avg_price from aptos.price.ez_prices_hourly where symbol = 'APT' ) , swap_volume as ( select date, tx_hash, swapper, token_in, symb_in, token_out, symb_out, amount_in, amount_out, amount_in_usd, amount_out_usd, amount_in*b.avg_price as amt_usd_in, amount_out*c.avg_price as amt_usd_out, case when amt_usd_in is null then amt_usd_out else amt_usd_in end as amount_usd_1, case when amount_in_usd is null then amount_out_usd else amount_in_usd end as amount_usd_2, case when amount_in_usd > amount_out_usd then amount_out_usd else amount_in_usd end as amount_usd_3, case when amount_usd_3 is null then amount_usd_2 else Amount_usd_3 end as amount_usd_4, case when amount_usd_1 is null then amount_usd_4 else amount_usd_1 end as volume from swaps a left outer join price b on a.date::date = b.day::date and a.token_in = b.tok_address left outer join price c on a.date::date = c.day::date and a.token_out = c.tok_address ) , final as (select DISTINCT tx_hash, date, swapper, token_in, symb_in, token_out, symb_out, volume from swap_volume where symb_out not in ('MGPT') and symb_in not in ('MGPT') ) select trunc(date,'week') as weekly, case when date >= '2022-01-01' and date < '2022-04-01' then 'Q1-2022' when date >= '2022-04-01' and date < '2022-07-01' then 'Q2-2022' when date >= '2022-07-01' and date < '2022-10-01' then 'Q3-2022' when date >= '2022-10-01' and date < '2023-01-01' then 'Q4-2022' when date >= '2023-01-01' and date < '2023-04-01' then 'Q1-2023' when date >= '2023-04-01' and date < '2023-07-01' then 'Q2-2023' when date >= '2023-07-01' and date < '2023-10-01' then 'Q3-2023' when date >= '2023-10-01' and date < '2024-01-01' then 'Q4-2023' when date >= '2024-01-01' and date < '2024-04-01' then 'Q1-2024' when date >= '2024-04-01' and date < '2024-07-01' then 'Q2-2024' when date >= '2024-07-01' and date < '2024-10-01' then 'Q3-2024' when date >= '2024-10-01' and date < '2025-01-01' then 'Q4-2024' end as type, count(DISTINCT tx_hash) as transactions, count(DISTINCT swapper) as swappers, sum(transactions) over (order by weekly asc) as "Cumulative Transactions", transactions/swappers as "Avg Swaps per Swapper", sum(volume) as "Volume (USD)", avg(volume) as "Avg Volume (USD)", median(volume) as "Median Volume (USD)", max(volume) as "Max Volume (USD)", count(DISTINCT token_in) as "Tokens (IN)", count(DISTINCT token_out) as "Tokens (OUT)", sum("Volume (USD)") over (partition by type order by weekly asc) as "Cumulative Volume" from final group by 1 ,2 order by 1 asc

Aptos: Swaps