SELECT

date_trunc('hour', RECORDED_AT) as day1,

avg(price) as price

FROM osmosis.core.dim_prices

LEFT outer JOIN osmosis.core.dim_labels

ON PROJECT_NAME LIKE symbol

WHERE symbol LIKE 'OSMO'

GROUP BY 1

), TAB1 AS (

SELECT

DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS DAY,

CASE WHEN not TO_CURRENCY LIKE 'uosmo' THEN TO_CURRENCY ELSE FROM_CURRENCY END AS token_address,

--MEDIAN(CASE WHEN not TO_CURRENCY LIKE '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 MEDIAN_PRICE,

AVG(CASE WHEN not TO_CURRENCY LIKE '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

LEFT outer JOIN osmosis.core.dim_labels

ON to_currency LIKE address

OR from_currency LIKE address

LEFT outer join price1

ON date_trunc('day', block_timestamp) = day1

WHERE PROJECT_NAME LIKE 'OSMO'

--AND block_timestamp > '2022-09-01'

GROUP BY 1,2

UNION

SELECT

date_trunc('hour', RECORDED_AT) as day,

'uosmo' as token_address,

avg(price) as avg_price

FROM osmosis.core.dim_prices

LEFT outer JOIN osmosis.core.dim_labels