with new as ( select min(block_timestamp::date) as date, from_address from ethereum.core.fact_transactions group by 2) , staking as ( select tx_hash from ethereum.core.fact_event_logs where contract_address in ( '0xae7ab96520de3a18e5e111b5eaab095312d7fe84','0x2cac916b2a963bf162f076c0a8a4a8200bcfbfb4', '0x00000000219ab540356cbb839cbe05303d7705fa') and block_timestamp::date >= CURRENT_DATE - 30 and event_name = 'Transfer' and event_inputs:from = '0x0000000000000000000000000000000000000000') , liquidity as ( select tx_hash from ethereum.core.fact_event_logs where event_name ilike '%liquidity%' and block_timestamp >= CURRENT_DATE - 30) , new_user as ( select DISTINCT from_address from new where date >= CURRENT_DATE - 30) , final as ( select date(block_timestamp) as date,'Swap' as type, tx_hash, origin_from_address as user, amount_in_usd as amount from ethereum.core.ez_dex_swaps where origin_from_address in (select from_address from new_user) and date >= CURRENT_DATE - 30 and amount_in_usd < 30000000 UNION select date(block_timestamp) as date,'NFT Sales' as type, tx_hash, buyer_address as user , PRICE_USD as amount from ethereum.core.ez_nft_sales where buyer_address in (select from_address from new_user) and date >= CURRENT_DATE - 30 UNION select date(block_timestamp) as date,'Mint NFT' as type, tx_hash, nft_to_address as user, MINT_PRICE_USD as amount from ethereum.core.ez_nft_mints where nft_to_address in (select from_address from new_user) and date >= CURRENT_DATE - 30 UNION select date(block_timestamp) as date,'Transfers' as type, tx_hash, from_address as user, AMOUNT_USD as amount from ethereum.core.ez_token_transfers where from_address in (select from_address from new_user) and date >= CURRENT_DATE - 30 and amount_usd < 1000000 UNION select date(block_timestamp) as date,'Transfers' as type, tx_hash, eth_from_address as user, AMOUNT_USD as amount from ethereum.core.ez_eth_transfers where eth_from_address in (select from_address from new_user) and date >= CURRENT_DATE - 30 and amount_usd < 1000000 UNION select date(block_timestamp) as date,'Stake' as type, tx_hash , origin_from_address as user, amount_usd as amount from ethereum.core.ez_eth_transfers where origin_to_address in ( '0xae7ab96520de3a18e5e111b5eaab095312d7fe84','0x2cac916b2a963bf162f076c0a8a4a8200bcfbfb4', '0x00000000219ab540356cbb839cbe05303d7705fa') and origin_from_address = eth_from_address and tx_hash in (select tx_hash from staking) and origin_from_address in (select from_address from new_user) UNION select date(block_timestamp) as date,'Liquidity' as type, tx_hash, from_address as user, AMOUNT_USD as amount from ethereum.core.ez_token_transfers where from_address in (select from_address from new_user) and amount_usd <= 10000000 and date >= CURRENT_DATE - 30 and tx_hash in (select tx_hash from liquidity) UNION select date(block_timestamp) as date,'Liquidity' as type, tx_hash, eth_from_address as user, AMOUNT_USD as amount from ethereum.core.ez_eth_transfers where eth_from_address in (select from_address from new_user) and date >= CURRENT_DATE - 30 and tx_hash in (select tx_hash from liquidity) and amount_usd <= 10000000 ) , finals as ( select type ,user , count(DISTINCT(tx_hash)) as total_tx, count(DISTINCT(user)) as total_user, sum(amount) as volume from final group by 1 ,2 having total_tx <= 1000) select type, count(DISTINCT(user)) as total, case when volume <= 1 then '1$' when volume <= 10 then '1-10$' when volume <= 100 then '10-100$' when volume <= 1000 then '100-1K$' when volume <= 10000 then '1K-10K$' when volume <= 100000 then '10K-100K$' else '+100K$' end as category from finals group by 1,3

Ethereum - Merry Christmas?