select

date_trunc('day', hour) as day,

case when token_address is null then 'ETH' else 'Matic' end as token,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

where token_address is null or token_address = '0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0'

group by 1, 2

),

all_day as (

select

date_trunc(hour, s.block_timestamp) as date,

case

when extract(dow from date) = 0 then '7.Sunday'

when extract(dow from date) = 1 then '1.Monday'

when extract(dow from date) = 2 then '2.Tuesday'

when extract(dow from date) = 3 then '3.Wednesday'

when extract(dow from date) = 4 then '4.Thursday'

when extract(dow from date) = 5 then '5.Friday'

when extract(dow from date) = 6 then '6.Saturday'

end as days,

date_part(hour, date) as hours,

avg(tx_fee * price) as avg_fee_usd

from arbitrum.sushi.ez_swaps s join arbitrum.core.fact_transactions t on s.tx_hash = t.tx_hash join token_price on s.block_timestamp::date = day

where token = 'ETH' and platform = 'sushiswap' and event_name = 'Swap' and tx_fee is not null

group by 1, 2, 3

select

days,

hours,

avg(avg_fee_usd) as avg_fee

from all_day

group by 1, 2

order by 1, 2