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)

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,

min (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 Minimum_USD_Volume,

max (case when token_in_contract = token_contract then token_in_amount

when token_out_contract = token_contract then token_out_amount end) as Maximum_Token_Volume,

max (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 Maximum_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