select

date_trunc('day', recorded_at) as day,

address,

symbol,

avg(price) as price

from osmosis.core.dim_labels join osmosis.core.dim_prices on symbol = project_name

group by 1, 2, 3

select

date_trunc('day', block_timestamp) as date,

case when date >= '2022-11-11' then 'After the program' else 'Before the program' end as period,

count(distinct tx_id) as tx_count,

count(distinct liquidity_provider_address) as unique_user,

sum(price * (amount/power(10, decimal))) as volume,

'Inflow' as type

from osmosis.core.fact_liquidity_provider_actions join price on address = currency and day = block_timestamp::date

where action = 'pool_joined' and tx_status = 'SUCCEEDED' and pool_id = '833' and date >= '2022-11-03'

group by 1

union all

select

date_trunc('day', block_timestamp) as date,

case when date >= '2022-11-11' then 'After the program' else 'Before the program' end as period,

-count(distinct tx_id) as tx_count,

-count(distinct liquidity_provider_address) as unique_user,

-sum(price * (amount/power(10, decimal))) as volume,

'Outflow' as type

from osmosis.core.fact_liquidity_provider_actions join price on address = currency and day = block_timestamp::date

where action = 'pool_exited' and tx_status = 'SUCCEEDED' and pool_id = '833' and date >= '2022-11-03'

group by 1, 2