date_trunc('day',RECORDED_HOUR) as day,

TOKEN,

ID as TOKEN_CONTRACT,

avg(CLOSE) as token_price

from flow.core.fact_hourly_prices

group by 1,2,3

),

tb2 as( select *,

TOKEN as token_in,

TOKEN_IN_AMOUNT*a.token_price as amount_in_usd

from flow.core.ez_swaps s left join tb1 a on s.TOKEN_IN_CONTRACT=a.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=a.day),

tb3 as( select *,

TOKEN as token_out,

TOKEN_out_AMOUNT*a.token_price as amount_out_usd

from flow.core.ez_swaps s left join tb1 a on s.TOKEN_out_CONTRACT=a.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=a.day)

select

CONCAT(token_in,' -> ',token_out) as asset_pairs,

count(distinct a.tx_id) as no_swaps,

sum(amount_in_usd) as volume_usd,

count(distinct a.trader) as no_traders,

no_traders/count(distinct date_trunc(day, a.block_timestamp)) as average_sender_day,

volume_usd/count(distinct date_trunc(day, a.block_timestamp)) as average_volume_day,

row_number() over (order by volume_usd desc) as rank

FROM tb2 a join tb3 b on a.tx_id = b.tx_id

and a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP

and a.token_in <> b.token_out

group by 1

order by 3 desc limit 10