with swap_tx as ( select date(block_timestamp) as date, trader, tx_id,from_currency, b.project_name as from_label, from_amount/pow(10,from_decimal) as from_amounts,to_currency, c.project_name as to_label, to_amount/pow(10,TO_DECIMAL) as to_amounts from osmosis.core.fact_swaps a join osmosis.core.dim_labels b on a.from_currency = b.address join osmosis.core.dim_labels c on a.to_currency = c.address where TX_SUCCEEDED = 'TRUE' and from_decimal is not null and to_decimal is not null and date >= CURRENT_DATE - 90 and date <= CURRENT_DATE - 2) , price as ( select date(recorded_hour) as date, symbol, currency , avg(price) as price from osmosis.core.ez_prices where date >= CURRENT_DATE - 90 and date <= CURRENT_DATE - 2 group by 1,2,3) , final as ( select a.date, trader, tx_id, from_currency, from_label, from_amounts, b.price as from_price , to_currency, to_label, to_amounts, c.price as to_price from swap_tx a left outer join price b on a.date = b.date and a.from_currency ilike b.currency left outer join price c on a.date = c.date and a.to_currency ilike c.currency ) , final_2 as ( select date, trader, tx_id, from_label, from_amounts, case when from_label in ('USDC.grv','DAI.grv','USDT.grv') then 1 else from_price end as From_prices, to_label, to_amounts, case when to_label in ('USDC.grv','DAI.grv','USDT.grv') then 1 else to_price end as to_prices from final) , final_3 as ( select date, trader, tx_id, from_label, from_amounts*from_prices as from_volume, From_prices, to_label, to_amounts*to_prices as to_volume, to_prices from final_2 where from_prices is not null) , final_4 as ( select trader, count(DISTINCT(tx_id)) as total_swaps, sum(from_volume) as total_volume, avg(from_volume) as avg_volume, median(from_volume) as median_volume, max(from_volume) as max_volume, min(from_volume) as min_amounts from final_3 group by 1) select count(DISTINCT(trader)) as total, case when total_volume <= 1 then 'a. Below 1$' when total_volume <= 10 then 'b. 1-10$' when total_volume <= 100 then 'c. 10-100$' when total_volume <= 1000 then 'd. 100-1K$' when total_volume <= 10000 then 'e. 1K-10K$' when total_volume <= 25000 then 'f. 10K-25K$' when total_volume <= 50000 then 'g. 25-50K$' when total_volume <= 100000 then 'h. 50-100K$' when total_volume > 100000 then 'i. +100K$' end as type from final_4 group by 2 having type is not null

Osmosis Swaps