select timestamp::Date as day,

token,

symbol,

token_contract,

avg (price_usd) as USDPrice

from flow.core.fact_prices

where token != 'Blocto'

group by 1,2,3,4),

maintable as (

select case when token_in_contract = token_contract then 'Swap From Token'

when token_out_contract = token_contract then 'Swap To Token'

else null end as swap_type,

tx_id,

trader,

case when token_in_contract = token_contract then token_in_amount when token_out_contract = token_contract then token_out_amount end as Token_Volume,

case when token_in_contract = token_contract then token_in_amount*usdprice when token_out_contract = token_contract then token_out_amount*usdprice end as USD_Volume

from flow.core.ez_swaps t1 join pricet t2 on (t1.token_in_contract = t2.token_contract or t1.token_out_contract = t2.token_contract) and t1.block_timestamp::Date = t2.day

where symbol ilike 'Flow')

select swap_type,

case when token_volume < 10 then 'Less Than 10Flow'

when token_volume >= 10 and token_volume <= 100 then '10 - 100 Flow '

when token_volume >= 100 and token_volume <= 1000 then '100 - 1000 Flow'

when token_volume >= 1000 and token_volume <= 10000 then '1000 - 10000 Flow'

else 'More Than 10000 ' end as volume_type,

count (distinct tx_id) as TX_Count

from maintable

group by 1,2