select

Date(date_trunc('day', recorded_at)) as date,

avg(price) as price_usd

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by Date),

lp_txns as (

select

Decode(pool_id,'1' ,'ATOM / OSMO','678','axlUSDC / OSMO','674','DAI / OSMO','560','USTC / OSMO','633','USTC.grv / OSMO','818','USDT.grv / OSMO','OTHER') as pool_name,

p.price_usd,

(2 * l.amount * p.price_usd/1e6) as amount_usd,

l.*

from osmosis.core.fact_liquidity_provider_actions l

join Pr p on l.block_timestamp::date = p.date

where 1=1

and pool_id in ('678','1','674','560','633','818')

and action = 'pool_joined'

and currency = 'uosmo'

and block_timestamp >= '2022-01-01')

select

Date(date_trunc('week',block_timestamp)) as date,

pool_name,

count(distinct tx_id) as n_txns,

count(distinct liquidity_provider_address) as n_lpers,

sum(amount_usd) as lp_amount_usd,

sum(lp_amount_usd) over (partition by pool_name order by date asc rows between unbounded preceding and current row) as cum_lp_amount_usd,

avg(lp_amount_usd) over (partition by pool_name order by date asc rows between unbounded preceding and current row) as avg_lp_amount_usd,

sum(n_lpers) over (partition by pool_name order by date asc rows between unbounded preceding and current row) as cum_n_lpers,

sum(n_txns) over (partition by pool_name order by date asc rows between unbounded preceding and current row) as cum_n_txns

from lp_txns

group by Date,Pool_name

Order by Date,Pool_name