select *

from osmosis.core.dim_prices

where symbol ilike 'atom'

SELECT block_timestamp::date as day_time,

sum(case when from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' and array_size(pool_ids) = 1 then from_amount/power(10,from_decimal)*px.price end)*-1 as atom_sold,

count(distinct case when from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then trader end) as sell_trader_count,

count(distinct case when to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then trader end) as buy_trader_count,

sum(case when to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' and array_size(pool_ids) = 1 then to_amount/power(10,to_decimal)*px.price end) as atom_bought,

avg(case when from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' or to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then array_size(pool_ids) end) as avg_num_pools,

median(case when from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' or to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then array_size(pool_ids) end) as median_num_pools,

mode(case when from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' or to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then array_size(pool_ids) end) as mode_num_pools,

atom_bought + atom_sold as net_atom_flow

FROM osmosis.core.fact_swaps swaps

JOIN atom_price px

ON date_trunc('hour', swaps.block_timestamp) = date_trunc('hour', px.recorded_at)

WHERE day_time >= current_date - INTERVAL '1 week'

GROUP BY 1