t1 as (

SELECT trunc(RECORDED_AT,'hour') as hours, avg(price) as price

FROM osmosis.core.dim_prices

JOIN osmosis.core.dim_labels ON project_name = symbol WHERE symbol = 'OSMO'

GROUP BY 1

),

t2 AS (

SELECT

trunc(block_timestamp,'day') as date,

CASE WHEN TO_CURRENCY <> 'uosmo' then TO_CURRENCY else FROM_CURRENCY end as token_address,

avg(case when TO_CURRENCY <> 'uosmo' then ((FROM_AMOUNT/POWER(10, FROM_DECIMAL)) * PRICE)/(TO_AMOUNT/POWER(10, TO_DECIMAL))

ELSE ((TO_AMOUNT/POWER(10, TO_DECIMAL)) * PRICE)/(FROM_AMOUNT/POWER(10, FROM_DECIMAL)) END) as avg_price

FROM osmosis.core.fact_swaps

JOIN osmosis.core.dim_labels ON (to_currency = address OR from_currency = address)

join t1 ON trunc(block_timestamp,'hour') = hours

WHERE project_name = 'OSMO'

GROUP BY 1,2

UNION

SELECT

trunc(RECORDED_AT,'hour') as hours,'uosmo' as token_address,avg(price) as avg_price

FROM osmosis.core.dim_prices

JOIN osmosis.core.dim_labels ON project_name = symbol

WHERE symbol = 'OSMO'

GROUP BY 1,2

),

t3 as (

SELECT

block_timestamp,

tx_id,

count(DISTINCT currency) as dual_deposits,

COUNT(1) as single_deposits,

sum(avg_price * (amount/power(10, decimal))) as volume

FROM osmosis.core.fact_liquidity_provider_actions

JOIN t2 ON trunc(block_timestamp,'day') = date AND token_address = currency

WHERE action LIKE 'pool_joined'