with PriceT1 as (

select recorded_at::date as Day1,

avg (price) as OSMOPrice

from osmosis.core.dim_prices t1 left join osmosis.core.dim_labels t2 on t1.symbol = t2.project_name

where symbol = 'OSMO'

group by 1 ),

PriceT2 as (

select date_trunc (week,block_timestamp) as date,

case when to_currency != 'uosmo' then to_currency

else from_currency end as token_address,

median (case when to_currency != 'uosmo' then ((from_amount/power(10,from_decimal)) * OSMOPrice)/(to_amount/power(10,to_decimal))

else ((to_amount/power(10,to_decimal)) * OSMOPrice)/(from_amount/power(10,from_decimal)) end) as USDPrice

from osmosis.core.fact_swaps t1 left join osmosis.core.dim_labels t2 on t1.to_currency = t2.address or t1.from_currency = t2.address

left join PriceT1 on block_timestamp::date = Day1

where project_name = 'OSMO'

group by 1,2),

PriceT3 as (

select date,

project_name as Asset_Name,

from PriceT2 t1 left join osmosis.core.dim_labels t2 on t1.token_address = t2.address),

Table1 as (

select block_timestamp,

tx_id,

pool_id,

sum (case when address is not NULL then USDPrice * (amount/power(10,decimal)) end) as Volume,

sum (case when address is not NULL then USDPrice * (amount/power(10,decimal)) end) / sum (case when address is NULL then (amount/power(10,decimal)) end) as Pool_Price_USD

from osmosis.core.fact_liquidity_provider_actions t1 left join osmosis.core.dim_labels t2 on t1.currency = t2.address

left join PriceT3 t3 on t2.project_name = t3.Asset_Name and t1.block_timestamp::Date = t3.date

group by 1,2,3),

Table2 as (