with atom_d as (

select

sum(case when from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then (from_amount/pow(10,6))

when to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then (to_amount/pow(10,6))

else null end ) as daily_atom_swap_amount,

count(distinct tx_id) as daily_swaps,

avg(case when from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then (from_amount/pow(10,6))

when to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then (to_amount/pow(10,6))

else null end ) as avg_atom_swap_per_tx,

date_trunc('day', block_timestamp) as date

from osmosis.core.fact_swaps

where from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2'

or to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2'

group by 4

),

atom_p as (

select

date_trunc('day', recorded_at) as date,

avg(price) as daily_price,

avg(case when volume_24h < 5000000000 then volume_24h else null end) as volume_24H

from osmosis.core.dim_prices

where symbol = 'ATOM'

group by 1

select

sum(daily_atom_swap_amount * daily_price) as weekly_atom_volume_on_osmosis,

avg(avg_atom_swap_per_tx * daily_price) as avg_dollar_volume_per_swap,

sum(daily_swaps) as weekly_count_of_swaps,

sum(volume_24H) as volume_7D,

avg(daily_price) as weekly_price,

sum(daily_atom_swap_amount) as weekly_atom_swap_amount,

avg(avg_atom_swap_per_tx) avg_atom_swap_per_tx,

date_trunc('week', p.date) as date