with transfers as (

select

tx_id,

block_timestamp

from sei.core.fact_transfers

where block_timestamp::date >= dateadd('day', -{{past_days}}, current_date)

and transfer_type = 'SEI'

,

avg_price_sei as (

SELECT

date_trunc('day',RECORDED_HOUR) as day,

avg(price) as usd_price

FROM osmosis.price.ez_prices

WHERE symbol = 'SEI'

and day >= current_date - 90

group by day

,

fee_sei as (

select

date_trunc('day', a.block_timestamp) as date,

avg(split(fee,'usei')[0] / pow(10,6)) as avg_fee_sei,

median(split(fee,'usei')[0] / pow(10,6)) as median_fee_sei

from sei.core.fact_transactions a

inner join transfers b using(tx_id, block_timestamp)

where block_timestamp::date >= dateadd('day', -{{past_days}}, current_date)

group by 1

select

date

,avg_fee_sei

,avg_fee_sei * b.usd_price as avg_fee_usd