select

date(recorded_at) as date0,

hour(recorded_at) as hour0,

SYMBOL,

avg(price) as hourly_price

from osmosis.core.dim_prices

where symbol in ('ATOM' ,'OSMO', 'EVMOS')

and recorded_at < CURRENT_DATE

and recorded_at >= CURRENT_DATE - 7

group by 1 , 2 , 3

order by 3 , 2 , 1),

swaps_from as (select

case

when FROM_CURRENCY = 'uosmo' then 'OSMO'

when FROM_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then 'ATOM'

when FROM_CURRENCY = 'ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A' then 'EVMOS'

end as token,

TX_ID,

TRADER as swapper,

FROM_AMOUNT/pow(10,FROM_DECIMAL) as token_amount,

POOL_IDS,

date(block_timestamp) as date,

hour(block_timestamp) as hour

from osmosis.core.fact_swaps a

where FROM_CURRENCY in ('uosmo','ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2','ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A')

and tx_status = 'SUCCEEDED'

and block_timestamp < CURRENT_DATE

and block_timestamp >= CURRENT_DATE - 7),

swaps_to as (select

case

when TO_CURRENCY = 'uosmo' then 'OSMO'

when TO_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then 'ATOM'

when TO_CURRENCY = 'ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A' then 'EVMOS'

end as token,

TX_ID,