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),

lps as (select

case

when currency = 'uosmo' then 'OSMO'

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

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

end as token,

TX_ID,

LIQUIDITY_PROVIDER_ADDRESS as lper,

ACTION,

AMOUNT/pow(10,decimal) as token_amount,

POOL_ID,

date(block_timestamp) as date,

hour(block_timestamp) as hour

from osmosis.core.fact_liquidity_provider_actions a

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

and tx_status = 'SUCCEEDED'

and action in ('pool_joined','pool_exited')

and block_timestamp < CURRENT_DATE

and block_timestamp >= CURRENT_DATE - 7)

select

POOL_ID as "Top Pools",

count (*) as "Number of LP Actions",

sum(token_amount) as "ATOM Volume",