with prep as( select date_trunc('day', block_timestamp) day, trader, tx_id, token_in_contract, token_in_amount from flow.core.ez_swaps where day >= current_date - {{days_back}} ), prices as( select date_trunc('day', recorded_hour) day, id, token, avg(close) price from flow.core.fact_hourly_prices where recorded_hour::date >= (select min(block_timestamp::date) from flow.core.ez_swaps) group by day, id, token ) select volume_type, count(trader) total_users from( select trader, sum(token_in_amount*price) volume_usd, case when volume_usd > 0 and volume_usd <= 20 then ' 0-20 $' when volume_usd > 20 and volume_usd <= 100 then ' 20-100 $' when volume_usd > 100 and volume_usd <= 500 then '100-500 $' when volume_usd > 500 and volume_usd <= 5000 then '500-5000 $' when volume_usd > 5000 then '5000+ $' end volume_type, count(distinct tx_id) total_tx, case when total_tx = 1 then ' 1 tx' when total_tx > 1 and total_tx <= 10 then ' 2-10' when total_tx > 10 and total_tx <= 50 then ' 10-50' when total_tx > 50 and total_tx <= 500 then ' 50-500' when total_tx > 500 and total_tx <= 5000 then ' 500-5000' when total_tx > 5000 then ' 5000+' end tx_type, count(distinct date_trunc('week', prep.day)) total_weeks, case when total_weeks = 1 then ' 1 week' when total_weeks > 1 and total_weeks <= 3 then ' 2-3 weeks' when total_weeks > 3 and total_weeks <= 5 then ' 4-5 weeks' when total_weeks > 5 and total_weeks <= 10 then '5-10 weeks' when total_weeks > 10 then '11+ weeks' end weeks_type from prep left join prices on prep.day = prices.day and prep.token_in_contract = prices.id group by trader having volume_usd > 0 ) group by volume_type order by total_users desc

Swaps activity on Flow