recorded_at::date as day,

avg(price) as price_usd

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by 1

select

liquidity_provider_address as user,

count(distinct iff(action = 'pool_joined', tx_id, null)) as inflow_transactions,

count(distinct iff(action = 'pool_exited', tx_id, null)) as outflow_transactions,

sum(iff(action = 'pool_joined', price_usd * (amount / pow(10, decimal)), 0)) as inflow_volume_usd,

sum(iff(action = 'pool_exited', price_usd * (amount / pow(10, decimal)), 0)) as outflow_volume_usd,

inflow_transactions - outflow_transactions as netflow_transactions,

inflow_volume_usd - outflow_volume_usd as netflow_volume

from osmosis.core.fact_liquidity_provider_actions

join t1

on block_timestamp::date = day

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

and block_timestamp > current_date - interval '3 months'

group by 1

order by netflow_transactions desc

limit 10