tx_id,

blockchain,

from_amount_usd, case

when from_amount_usd between 0 and 100 then '1_Less_than_$100'

when from_amount_usd between 100 and 1000 then '2_Less_than_$1000'

when from_amount_usd between 1000 and 10000 then '3_Less_than_$10,000'

when from_amount_usd between 10000 and 1000000 then '4_Less_than_$100,000'

when from_amount_usd between 100000 and 1000000 then '5_Less_than_$1,000,000'

else '6_Over_$1,000,000' end as categories

from thorchain.swaps

where from_amount_usd > 0 and block_timestamp >= CURRENT_DATE - 30),

total as (select

blockchain,

sum(from_amount_usd) as total_vol,

count(distinct tx_id) as total_count

from thorchain.swaps

where from_amount_usd > 0 and block_timestamp >= CURRENT_DATE - 30

group by 1),

final as (select

a.blockchain, a.categories, count(distinct a.tx_id) as swap_count, sum(a.from_amount_usd) as volume_usd, case

when a.blockchain = b.blockchain then b.total_vol else null end as total_vol_day, case

when a.blockchain = b.blockchain then b.total_count else null end as total_count

from cat a

left join total b on a.blockchain = b.blockchain

group by 1,2,5,6)

select blockchain, categories, swap_count, volume_usd, total_vol_day, total_count,

(volume_usd/total_vol_day)*100 as percentage_volume,

(swap_count/total_count)*100 as percentage_count

from final