with source4 as ( select distinct trader from crosschain.defi.ez_dex_swaps where platform ilike '%uniswap%' and blockchain in ('ethereum', 'optimism', 'arbitrum', 'polygon') and amount_in_usd <= 15000000 and amount_out_usd <= 15000000 and block_timestamp::date >= '2022-01-01' ), inactive_13_to_15_months as ( select distinct trader, count(distinct date_trunc('month', block_timestamp)) as active_month_count from crosschain.defi.ez_dex_swaps where platform ilike '%uniswap%' and blockchain in ('ethereum', 'optimism', 'arbitrum', 'polygon') and amount_in_usd <= 15000000 and amount_out_usd <= 15000000 and block_timestamp::date >= '2022-06-01' and trader in (select distinct trader from source4) group by 1 having active_month_count <= 3 -- Users that were inactive for 13-15 months ), source1 as ( select distinct trader from crosschain.defi.ez_dex_swaps where platform ilike '%uniswap%' and blockchain in ('ethereum', 'optimism', 'arbitrum', 'polygon') and amount_in_usd <= 15000000 and amount_out_usd <= 15000000 and block_timestamp::date >= '2022-06-01' ), source2 as ( select distinct trader from crosschain.defi.ez_dex_swaps where platform ilike '%uniswap%' and blockchain in ('ethereum', 'optimism', 'arbitrum', 'polygon') and amount_in_usd <= 15000000 and amount_out_usd <= 15000000 and block_timestamp::date >= '2022-01-01' and block_timestamp::date < '2022-06-01' and trader not in (select distinct trader from source1) union all select distinct trader from inactive_13_to_15_months ), source3 as ( select count(distinct trader) as trader_count, case when tx = 1 then '1' when tx >= 2 and tx <= 5 then '2-5' when tx > 5 then '>5' end as tx from ( select distinct trader, count(distinct tx_hash) as tx from crosschain.defi.ez_dex_swaps where platform not ilike '%uniswap%' and blockchain in ('ethereum', 'optimism', 'arbitrum', 'polygon') and amount_in_usd <= 15000000 and amount_out_usd <= 15000000 and block_timestamp::date >= '2022-06-01' and trader in (select distinct trader from source2) group by 1 ) group by 2 ) select (inactive_traders - traders) as trader_count, '0' as tx from ( select sum(trader_count) traders from source3 ) x join ( select count(distinct trader) as inactive_traders from (select distinct trader from source2 union all select distinct trader from inactive_13_to_15_months) ) union all select * from source3

Unicorn vs hungry bears