symbol,

avg(price) as token_price

from osmosis.core.dim_prices

where day >= CURRENT_DATE - 7

and symbol = 'ATOM'

group by 1,2)

,

lp as ( select trunc(block_timestamp,'day') as day,

liquidity_provider_address as wallet,

tx_id,

action,

project_name as token,

amount/pow(10,decimal) as amt

from osmosis.core.fact_liquidity_provider_actions a join

osmosis.core.dim_labels b on a.CURRENCY = b.address

and tx_status = 'SUCCEEDED'

and block_timestamp::date >= CURRENT_DATE - 7

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

,

volume as ( select a.day,

wallet,

tx_id,

token,

action,

amt,

amt*token_price as volume

from lp a left outer join atom_price b on a.day = b.day

where token = 'ATOM')

action,

count(DISTINCT wallet) as users,

count(DISTINCT tx_id) as count_tx,

sum(volume) as total_volume,

avg(volume) as avg_volume