WITH prices as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'gear.enleap.near' group by 1, 2 ), near_price as ( select hour::date as date, 'Near' as symbol, min(price) as near_price from near.price.ez_prices_hourly where symbol = 'NEAR' and hour::date >= '2024-01-01' group by 1, 2 ), gear as ( select a.date, 'gear.enleap.near' as tok_address, price * near_price as price from prices a join near_price b on a.date = b.date ) , uwon_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = '438e48ed4ce6beecf503d43b9dbd3c30d516e7fd.factory.bridge.near' group by 1, 2 ), uwon as ( select a.date, '438e48ed4ce6beecf503d43b9dbd3c30d516e7fd.factory.bridge.near' as tok_address, price * price as price from uwon_price a join near_price b on a.date = b.date ) , usm_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'usm.tkn.near' group by 1, 2 ), usm as ( select a.date, 'usm.tkn.near' as tok_address, price * price as price from usm_price a join near_price b on a.date = b.date ) , dd_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'dd.tg' group by 1, 2 ), dd as ( select a.date, 'dd.tg' as tok_address, price * price as price from dd_price a join near_price b on a.date = b.date ) , lonk_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'token.lonkingnearbackto2024.near' group by 1, 2 ), lonk as ( select a.date, 'token.lonkingnearbackto2024.near' as tok_address, price * price as price from lonk_price a join near_price b on a.date = b.date ) , bendog_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'benthedog.near' group by 1, 2 ), bendog as ( select a.date, 'benthedog.near' as tok_address, price * price as price from bendog_price a join near_price b on a.date = b.date ) , shitzu_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'token.0xshitzu.near' group by 1, 2 ), shitzu as ( select a.date, 'token.0xshitzu.near' as tok_address, price * price as price from bendog_price a join near_price b on a.date = b.date ) , touched_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'touched.tkn.near' group by 1, 2 ), touched as ( select a.date, 'touched.tkn.near' as tok_address, price * price as price from touched_price a join near_price b on a.date = b.date ) , kat_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'kat.token0.near' group by 1, 2 ), kat as ( select a.date, 'kat.token0.near' as tok_address, price * price as price from kat_price a join near_price b on a.date = b.date ) , nearnvidia_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'nearnvidia.near' group by 1, 2 ), nearnvidia as ( select a.date, 'nearnvidia.near' as tok_address, price * price as price from nearnvidia_price a join near_price b on a.date = b.date ) , intel_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'intel.tkn.near' group by 1, 2 ), intel as ( select a.date, 'intel.tkn.near' as tok_address, price * price as price from intel_price a join near_price b on a.date = b.date ) , nkok_price as ( select date(block_timestamp) as date, TOKEN_IN_CONTRACT, (sum(AMOUNT_OUT)/sum(AMOUNT_IN)) as price from near.defi.ez_dex_swaps where SYMBOL_OUT in ('wNEAR') and AMOUNT_OUT > 0 and AMOUNT_IN > 0 and TOKEN_IN_CONTRACT = 'nkok.tkn.near' group by 1, 2 ), nkok as ( select a.date, 'nkok.tkn.near' as tok_address, price * price as price from nkok_price a join near_price b on a.date = b.date ) , price as ( select * from nkok UNION select * from intel UNION select * from nearnvidia UNION select * from kat UNION select * from touched UNION select * from shitzu UNION select * from bendog UNION select * from lonk UNION select * from usm UNION select * from gear UNION select * from dd UNION select * from uwon UNION select hour::date as date, token_address as tok_address, avg(price) as price from near.price.ez_prices_hourly where blockchain = 'near' and token_address not in ('23.contract.portalbridge.near') group by 1,2 UNION select hour::date as date, '23.contract.portalbridge.near' as tok_address, avg(price) as price from near.price.ez_prices_hourly where symbol = 'AURORA' group by 1,2 UNION select hour::date as date, 'aurora' as tok_address, avg(price) as price from ethereum.price.ez_prices_hourly where symbol = 'WETH' group by 1,2 ) , trading as ( select block_timestamp::Date as date, pool_id, trader, tx_hash, amount_out, amount_in, amount_out_usd, amount_in_usd, amount_in*b.price as amountInUSD, amount_out*c.price as amountOutUSD, case when symbol_in in ('DAI','FRAX','USDC.e','USDC','USDt') then amount_in else amount_in_usd end as amount1, case when symbol_out in ('DAI','FRAX','USDC.e','USDC','USDt') then amount_out else amount_out_usd end as amount2, token_in_contract, token_out_contract, case when token_in_contract = 'usmeme.tg' then 'USM' when token_in_contract = 'xtoken.ref-finance.near' then 'REF' when token_in_contract = 'token.jumbo_exchange.near' then 'JUMBO' when token_in_contract = 'mi.tkn.near' then 'MI' else symbol_in end as symbol1, case when token_out_contract = 'usmeme.tg' then 'USM' when token_out_contract = 'token.jumbo_exchange.near' then 'JUMBO' when token_out_contract = 'xtoken.ref-finance.near' then 'REF' when token_out_contract = 'mi.tkn.near' then 'MI' else symbol_out end as symbol2, symbol_in, symbol_out, case when amount1 is null then amount2 else amount1 end as amount_usd1, case when amountInUSD is null then amountOutUSD else amountInUSD end as amount_usd2, case when amount_usd1 is null then amount_usd2 else amount_usd1 end as amount_usd from near.defi.ez_dex_swaps a left outer join price b on a.block_timestamp::date = b.date and a.token_in_contract = b.tok_address left outer join price c on a.block_timestamp::date = c.date and a.token_out_contract = c.tok_address where symbol_in not in ('PEM','marmaj') and symbol_out not in ('PEM','marmaj') ) 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 trader) as swappers, count(DISTINCT tx_hash) as swaps, sum(swaps) over (order by weekly asc) as "Cumulative Swaps", swaps/swappers as "Avg Swap Per User", sum(amount_usd) as "Volume (USD)", "Volume (USD)"/swappers as "Avg Volume per User", avg(amount_usd) as "Avg Swap Volume (USD)", median(amount_usd) as "Median Volume (USD)", max(amount_usd) as "Max Volume (USD)", sum("Volume (USD)") over (partition by type order by weekly asc) as cumulative_volume from trading where date >= '2023-01-01' GROUP by 1,2 order by 1 asc

Query	Creator	Created
Weekly Trading Volume and User Activity by Quarter near ethereum swaps evm defi	utkarshshahi	Oct 4, 2024
Cumulative volume per quarter near ethereum swaps evm defi	utkarshshahi	Oct 4, 2024

utkarshshahi