select recorded_at::date as day,

avg (price) as SCRTPrice

from osmosis.core.dim_prices

where symbol = 'SCRT'

and recorded_at >= CURRENT_DATE - 7

group by 1)

select action,

count (distinct tx_id) as TX_Count,

count (distinct liquidity_provider_address) as LPers_Count,

sum ((amount/1e6)*SCRTPrice) as Total_USD_Volume,

avg ((amount/1e6)*SCRTPrice) as Average_USD_Volume

from osmosis.core.fact_liquidity_provider_actions t1 join SCRTpricet t2 on t1.block_timestamp::date = t2.day

where currency = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A'

and tx_status = 'SUCCEEDED'

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

and block_timestamp >= CURRENT_DATE - 7

group by 1