with swaps as (

select

tx_id,

block_timestamp

from sei.defi.fact_dex_swaps

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

,

sei_price as (

SELECT 'SEI' as network,

RECORDED_HOUR as date,

price as last_price

FROM osmosis.price.ez_prices

WHERE symbol = 'SEI'

qualify row_number () over (order by date DESC) = 1

,

fee_sei as (

select

count(DISTINCT a.tx_id) as n_swaps,

sum(split(fee,'usei')[0] / pow(10,6)) as total_fee_sei,

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 swaps b using(tx_id, block_timestamp)

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

select

network

,date

,last_price

,n_swaps

,total_fee_sei