t1 as (

select date_trunc('week',recorded_at) as days,address,symbol,avg(price) as price_usd

from osmosis.core.dim_prices t1

join osmosis.core.dim_labels t2 on t1.symbol = t2.project_name

group by 1,2,3

union all

select date_trunc('week',recorded_at) as days,'ibc/D176154B0C63D1F9C6DCFB4F70349EBF2E2B5A87A05902F57A6AE92B863E9AEC' as address,symbol,avg(price) as price_usd

from osmosis.core.dim_prices t1

join osmosis.core.dim_labels t2 on t1.symbol = t2.project_name

where symbol = 'OSMO'

group by 1,2,3

union all

select date_trunc('week',recorded_at) as days,'ibc/92BE0717F4678905E53F4E45B2DED18BC0CB97BF1F8B6A25AFEDF3D5A879B4D5' as address,'IST' as symbol,1 as price_usd

from osmosis.core.dim_prices

group by 1,2,3

union all

select date_trunc('week',recorded_at) as days,'ibc/65381C5F3FD21442283D56925E62EA524DED8B6927F0FF94E21E0020954C40B5' as address,'WETH.grv' as symbol,1 as price_usd

from osmosis.core.dim_prices

where symbol = 'WETH'

group by 1,2,3

),

t2 as (

select liquidity_provider_address,

min (block_timestamp) as debut

from osmosis.core.fact_liquidity_provider_actions

where pool_id = [{{pool_id}}] and action = 'pool_joined'

group by 1

)

select date_trunc('week',block_timestamp) as days,

action,

count (distinct tx_id) as transactions,

count (distinct t1.liquidity_provider_address) as users,

sum(amount*price_usd/pow(10,decimal)) as volume,

avg(amount*price_usd/pow(10,decimal)) as avg_volume

from osmosis.core.fact_liquidity_provider_actions t1