with

price as (

select trunc(recorded_at,'hour') as dates,

avg(price) as avg_prices

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by 1

),

price_tb as (

select date(block_timestamp) as dates,

case when not to_currency = 'uosmo' then to_currency else FROM_CURRENCY end as token,

avg(case when not to_currency = 'uosmo' then ((from_amount/pow(10,from_decimal))*avg_prices)/(to_amount/pow(10, to_decimal))

else ((to_amount/pow(10,to_decimal))*avg_prices)/(from_amount/pow(10,from_decimal)) end) as avg_price

from osmosis.core.fact_swaps a

left outer join osmosis.core.dim_labels b on (a.to_currency = b.address or a.from_currency = b.address)

left outer join price c on a.block_timestamp::date = c.dates

where PROJECT_NAME = 'OSMO'

group by 1,2

),

price_pool as (

select dates as date,

project_name,

avg_price

from price_tb a

join osmosis.core.dim_labels b on a.token = b.address

),

transactions as (

select block_timestamp, tx_id,

pool_id,

sum(case when address is null then (amount/power(10, decimal)) end) as pool_token,

sum(case when not address is null then c.avg_price*(amount/power(10,decimal)) end) as volume,

sum(case when not address is null then c.avg_price*(amount/power(10,decimal)) end)/sum(case when address is null then (amount/power(10,decimal)) end) as pool_volume

from osmosis.core.fact_liquidity_provider_actions

left outer join osmosis.core.dim_labels b on currency = address

left outer join price_pool c on b.project_name = c.project_name and date = block_timestamp::date