select recorded_hour::date as day,

token,

case when token = 'Blocto' then 'A.0f9df91c9121c460.BloctoToken'

when token = 'FUSD' then 'A.3c5959b568896393.FUSD'

when token = 'Flow' then 'A.1654653399040a61.FlowToken'

when token = 'Rally' then 'A.231cc0dbbcffc4b7.RLY'

when token = 'Revv' then 'A.d01e482eb680ec9f.REVV'

when token = 'Sportium' then 'A.475755d2c9dccc3a.TeleportedSportiumToken'

when token = 'Starly' then 'A.142fa6570b62fd97.StarlyToken'

when token = 'USDC' then 'A.b19436aae4d94622.FiatToken'

when token = 'USDT' then 'A.cfdd90d4a00f7b5b.TeleportedTetherToken'

end as token_contract,

avg (close) as USDPrice

from flow.core.fact_hourly_prices

group by 1,2,3)

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,

count (distinct tx_id) as Swaps_Count,

count (distinct trader) as Swappers_Count,

sum (case when token_in_contract = token_contract then token_in_amount

when token_out_contract = token_contract then token_out_amount end) as Total_Token_Volume,

sum (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 Total_USD_Volume,

avg (case when token_in_contract = token_contract then token_in_amount

when token_out_contract = token_contract then token_out_amount end) as Average_Token_Volume,

avg (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 Average_USD_Volume,

median (case when token_in_contract = token_contract then token_in_amount

when token_out_contract = token_contract then token_out_amount end) as Median_Token_Volume,

median (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 Median_USD_Volume,

min (case when token_in_contract = token_contract then token_in_amount

when token_out_contract = token_contract then token_out_amount end) as Minimum_Token_Volume,