WITH priceTb as ( SELECT HOUR::date as p_date, symbol, median(price) as price FROM ethereum.core.fact_hourly_token_prices WHERE symbol in ('WETH', 'MATIC', 'WBNB', 'WBTC') AND HOUR::date >= '2022-12-26' AND HOUR::date <= CURRENT_DATE - 1 GROUP BY 1 ,2 ) , bsc1 as ( SELECT trunc(block_timestamp,'day') as date, count(distinct tx_hash) as tx_cnt, count(distinct from_address) as usr_cnt, tx_cnt/usr_cnt as avg_tx_per_user, sum((tx_fee/pow(10,6))*price) as fees_usd, avg((tx_fee/pow(10,6))*price) as avg_fee_usd, sum(tx_cnt) over (order by date) as cum_tx_cnt, sum(fees_usd) over (order by date) as cum_fees_usd from bsc.core.fact_transactions bsc join priceTb p on bsc.block_timestamp::date = p.p_date where symbol = 'WBNB' and block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 group by 1 ), bsc2 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as swap_tx_cnt, count(distinct ORIGIN_FROM_ADDRESS) as swapper_cnt, swap_tx_cnt/swapper_cnt as avg_swaps_per_swapper, sum(swap_tx_cnt) over (order by date) as cum_swap_tx_cnt from bsc.core.fact_event_logs where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and event_name='Swap' group by 1 ), bsc3 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as nft_sales_tx, count(distinct origin_from_address) as nft_buyer_cnt, nft_sales_tx/nft_buyer_cnt as nft_bought_per_user, sum(nft_sales_tx) over (order by date) as cum_nft_sales_tx from bsc.core.fact_event_logs bsc join bsc.core.dim_labels l on bsc.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='nft' group by 1 ), bsc4 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as defi_txs, count(distinct origin_from_address) as defi_users, defi_txs/defi_users as defi_txs_user, sum(defi_txs) over (order by date) as cum_defi_txs from bsc.core.fact_event_logs bsc join bsc.core.dim_labels l on bsc.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='defi' group by 1 ), bscFinal as ( SELECT bsc1.*, swap_tx_cnt, swapper_cnt, avg_swaps_per_swapper, cum_swap_tx_cnt, nft_sales_tx, nft_buyer_cnt, nft_bought_per_user, cum_nft_sales_tx, defi_txs, defi_users, defi_txs_user, cum_defi_txs from bsc1,bsc2,bsc3,bsc4 where bsc1.date=bsc2.date and bsc1.date=bsc3.date and bsc1.date=bsc4.date order by 1 asc ), eth1 as ( SELECT trunc(block_timestamp,'day') as date, count(distinct tx_hash) as tx_cnt, count(distinct from_address) as usr_cnt, tx_cnt/usr_cnt as avg_tx_per_user, sum((tx_fee/pow(10,6))*price) as fees_usd, avg((tx_fee/pow(10,6))*price) as avg_fee_usd, sum(tx_cnt) over (order by date) as cum_tx_cnt, sum(fees_usd) over (order by date) as cum_fees_usd from ethereum.core.fact_transactions eth join priceTb p on eth.block_timestamp::date = p.p_date where symbol = 'WETH' and block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 GROUP by 1 ), eth2 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as swap_tx_cnt, count(distinct ORIGIN_FROM_ADDRESS) as swapper_cnt, swap_tx_cnt/swapper_cnt as avg_swaps_per_swapper, sum(swap_tx_cnt) over (order by date) as cum_swap_tx_cnt from ethereum.core.fact_event_logs where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and event_name='Swap' group by 1 ), eth3 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as nft_sales_tx, count(distinct origin_from_address) as nft_buyer_cnt, nft_sales_tx/nft_buyer_cnt as nft_bought_per_user, sum(nft_sales_tx) over (order by date) as cum_nft_sales_tx from ethereum.core.fact_event_logs eth join ethereum.core.dim_labels l on eth.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='nft' group by 1 ), eth4 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as defi_txs, count(distinct origin_from_address) as defi_users, defi_txs/defi_users as defi_txs_user, sum(defi_txs) over (order by date) as cum_defi_txs from ethereum.core.fact_event_logs eth join ethereum.core.dim_labels l on eth.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='defi' group by 1 ), ethFinal as ( SELECT eth1.*, swap_tx_cnt, swapper_cnt, avg_swaps_per_swapper, cum_swap_tx_cnt, nft_sales_tx, nft_buyer_cnt, nft_bought_per_user, cum_nft_sales_tx, defi_txs, defi_users, defi_txs_user, cum_defi_txs from eth1,eth2,eth3,eth4 where eth1.date=eth2.date and eth1.date=eth3.date and eth1.date=eth4.date order by 1 asc ), arbi1 as ( SELECT trunc(block_timestamp,'day') as date, count(distinct tx_hash) as tx_cnt, count(distinct from_address) as usr_cnt, tx_cnt/usr_cnt as avg_tx_per_user, sum((tx_fee/pow(10,6))*price) as fees_usd, avg((tx_fee/pow(10,6))*price) as avg_fee_usd, sum(tx_cnt) over (order by date) as cum_tx_cnt, sum(fees_usd) over (order by date) as cum_fees_usd from arbitrum.core.fact_transactions arbi join priceTb p on arbi.block_timestamp::date = p.p_date where symbol = 'WETH' and block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 GROUP by 1 ), arbi2 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as swap_tx_cnt, count(distinct ORIGIN_FROM_ADDRESS) as swapper_cnt, swap_tx_cnt/swapper_cnt as avg_swaps_per_swapper, sum(swap_tx_cnt) over (order by date) as cum_swap_tx_cnt from arbitrum.core.fact_event_logs where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and event_name='Swap' group by 1 ), arbi3 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as nft_sales_tx, count(distinct origin_from_address) as nft_buyer_cnt, nft_sales_tx/nft_buyer_cnt as nft_bought_per_user, sum(nft_sales_tx) over (order by date) as cum_nft_sales_tx from arbitrum.core.fact_event_logs arbi join arbitrum.core.dim_labels l on arbi.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='nft' group by 1 ), arbi4 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as defi_txs, count(distinct origin_from_address) as defi_users, defi_txs/defi_users as defi_txs_user, sum(defi_txs) over (order by date) as cum_defi_txs from arbitrum.core.fact_event_logs arbi join arbitrum.core.dim_labels l on arbi.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='defi' group by 1 ), arbiFinal as ( SELECT arbi1.*, swap_tx_cnt, swapper_cnt, avg_swaps_per_swapper, cum_swap_tx_cnt, nft_sales_tx, nft_buyer_cnt, nft_bought_per_user, cum_nft_sales_tx, defi_txs, defi_users, defi_txs_user, cum_defi_txs from arbi1,arbi2,arbi3,arbi4 where arbi1.date=arbi2.date and arbi1.date=arbi3.date and arbi1.date=arbi4.date order by 1 asc ), op1 as ( SELECT trunc(block_timestamp,'day') as date, count(distinct tx_hash) as tx_cnt, count(distinct from_address) as usr_cnt, tx_cnt/usr_cnt as avg_tx_per_user, sum((tx_fee/pow(10,6))*price) as fees_usd, avg((tx_fee/pow(10,6))*price) as avg_fee_usd, sum(tx_cnt) over (order by date) as cum_tx_cnt, sum(fees_usd) over (order by date) as cum_fees_usd from optimism.core.fact_transactions op join priceTb p on op.block_timestamp::date = p.p_date where symbol = 'WETH' and block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 group by 1 ), op2 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as swap_tx_cnt, count(distinct ORIGIN_FROM_ADDRESS) as swapper_cnt, swap_tx_cnt/swapper_cnt as avg_swaps_per_swapper, sum(swap_tx_cnt) over (order by date) as cum_swap_tx_cnt from optimism.core.fact_event_logs where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and event_name='Swap' group by 1 ), op3 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as nft_sales_tx, count(distinct origin_from_address) as nft_buyer_cnt, nft_sales_tx/nft_buyer_cnt as nft_bought_per_user, sum(nft_sales_tx) over (order by date) as cum_nft_sales_tx from optimism.core.fact_event_logs op join optimism.core.dim_labels l on op.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='nft' group by 1 ), op4 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as defi_txs, count(distinct origin_from_address) as defi_users, defi_txs/defi_users as defi_txs_user, sum(defi_txs) over (order by date) as cum_defi_txs from optimism.core.fact_event_logs op join optimism.core.dim_labels l on op.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='defi' group by 1 ), opFinal as ( SELECT op1.*, swap_tx_cnt, swapper_cnt, avg_swaps_per_swapper, cum_swap_tx_cnt, nft_sales_tx, nft_buyer_cnt, nft_bought_per_user, cum_nft_sales_tx, defi_txs, defi_users, defi_txs_user, cum_defi_txs from op1,op2,op3,op4 where op1.date=op2.date and op1.date=op3.date and op1.date=op4.date order by 1 asc ), poly1 as ( SELECT trunc(block_timestamp,'day') as date, count(distinct tx_hash) as tx_cnt, count(distinct from_address) as usr_cnt, tx_cnt/usr_cnt as avg_tx_per_user, sum((tx_fee/pow(10,6))*price) as fees_usd, avg((tx_fee/pow(10,6))*price) as avg_fee_usd, sum(tx_cnt) over (order by date) as cum_tx_cnt, sum(fees_usd) over (order by date) as cum_fees_usd from polygon.core.fact_transactions poly join priceTb p on poly.block_timestamp::date = p.p_date where symbol = 'MATIC' and block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 group by 1 ), poly2 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as swap_tx_cnt, count(distinct ORIGIN_FROM_ADDRESS) as swapper_cnt, swap_tx_cnt/swapper_cnt as avg_swaps_per_swapper, sum(swap_tx_cnt) over (order by date) as cum_swap_tx_cnt from polygon.core.fact_event_logs where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and event_name='Swap' group by 1 ), poly3 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as nft_sales_tx, count(distinct origin_from_address) as nft_buyer_cnt, nft_sales_tx/nft_buyer_cnt as nft_bought_per_user, sum(nft_sales_tx) over (order by date) as cum_nft_sales_tx from polygon.core.fact_event_logs poly join polygon.core.dim_labels l on poly.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='nft' group by 1 ), poly4 as ( select trunc(block_timestamp,'day') as date, count(distinct tx_hash) as defi_txs, count(distinct origin_from_address) as defi_users, defi_txs/defi_users as defi_txs_user, sum(defi_txs) over (order by date) as cum_defi_txs from polygon.core.fact_event_logs poly join polygon.core.dim_labels l on poly.contract_address=l.address where block_timestamp >= '2022-12-26' AND block_timestamp <= CURRENT_DATE - 1 and label_type='defi' group by 1 ), polyFinal as ( SELECT poly1.*, swap_tx_cnt, swapper_cnt, avg_swaps_per_swapper, cum_swap_tx_cnt, nft_sales_tx, nft_buyer_cnt, nft_bought_per_user, cum_nft_sales_tx, defi_txs, defi_users, defi_txs_user, cum_defi_txs from poly1,poly2,poly3,poly4 where poly1.date=poly2.date and poly1.date=poly3.date and poly1.date=poly4.date order by 1 asc ) select 'BSC' as network,* from bscFinal union select 'Ethereum' as network,* from ethFinal union select 'Arbitrum' as network,* from arbiFinal union select 'Optimism' as network,* from opFinal union select 'Polygon' as network,* from polyFinal order by 2 asc,1

EVM - 3. Merry Christmas?