with scr_prices as (select

date(recorded_at) as days,

avg(price) as price,

sum(VOLUME_24H) as vol

from

osmosis.core.dim_prices

where

symbol in ('SCRT' )

and days >= CURRENT_DATE - 7

group by 1

order by 1)

select

pool_id,

count (distinct tx_id) as txs,

count (distinct LIQUIDITY_PROVIDER_ADDRESS) as LPers,

sum (AMOUNT/1e6) as native_volume,

sum ((AMOUNT/1e6)*price) as USD_Volume

from osmosis.core.fact_liquidity_provider_actions sw

inner join scr_prices pr on sw.block_timestamp::date = pr.days

where

currency = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A'

and

block_timestamp >= CURRENT_DATE - 7

ACTION = 'pool_exited'

group by 1 order by 2 desc limit 10