with tb1 as ( select trunc(block_timestamp,'day') as day, tx_hash, swapper, --case when amount_in_usd is null then amount_out_usd else amount_in_usd end as amount_usd, concat(symbol_in,'/',symbol_out) as pair, symbol_in, amount_in, amount_out, token_in, token_out, symbol_out, platform, amount_in_usd, amount_out_usd from aptos.defi.ez_dex_swaps --where (AMOUNT_IN_USD between AMOUNT_OUT_USD - 5000 and AMOUNT_OUT_USD + 5000) -- Credited to Abbas from old Dashboards where platform = 'thala' ) , tb2 as ( select trunc(hour,'day') as day, token_address, avg(price) as token_price from aptos.price.ez_prices_hourly group by 1,2 ) , tb3 as ( select a.*, amount_in_usd*b.token_price as amount_in_usd0, amount_out_usd*c.token_price as amount_out_usd0, case when symbol_in = 'USDC' then amount_in else amount_in_usd end as amount_usd0, case when symbol_out = 'USDC' then amount_out else amount_out_usd end as amount_usd1, case when amount_usd0 is null then amount_usd1 else amount_usd0 end as amount0, case when amount_in_usd is null then amount_out_usd else amount_in_usd end as amount1, case when amount0 is null then amount1 else amount0 end as amount_usd from tb1 a left outer join tb2 b on a.token_in = b.token_address and a.day = b.day left outer join tb2 c on a.token_out = c.token_address and a.day = c.day ) , tb4 as ( select day, count(DISTINCT tx_hash) as swaps, count(DISTINCT swapper) as swappers, sum(amount_usd) as "Volume (USD)", avg(amount_usd) as "Avg Volume (USD)", median(amount_usd) as "Median Volume (USD)", max(amount_usd) as "Max volume (USD)", avg("Volume (USD)") over (order by day rows between 7 preceding and current row) as avg_7_day_moving, avg("Volume (USD)") over (order by day rows between 14 preceding and current row) as avg_14_day_moving, avg("Volume (USD)") over (order by day rows between 30 preceding and current row) as avg_30_day_moving, count(DISTINCT token_in) as "Tokens (IN)", count(DISTINCT token_out) as "Tokens (OUT)" from tb3 group by 1) , tb5 as ( select swapper, sum(amount_usd) as volume from tb3 where amount_usd is not null group by 1) select count(DISTINCT swapper) as swappers, case when volume <= 10 then 'a. <= $10' when volume <= 50 then 'b. $10-$50' when volume <= 100 then 'c. $50-$100' when volume <= 500 then 'd. $100-$500' when volume <= 1000 then 'e. $500-$1k' when volume <= 10000 then 'f. $1k-$10k' when volume <= 50000 then 'g. $10k-$50k' when volume <= 100000 then 'h. $50k-$100k' when volume <= 500000 then 'i. $100k-$500k' when volume > 500000 then 'j. > $500k' end as breakdown from tb5 group by 2 order by 2 asc

Thalalabs on Aptos!