SELECT

DATE_TRUNC('day', HOUR) AS period,

AVG(PRICE) AS avg_uni_price

FROM ethereum.price.ez_prices_hourly

WHERE TOKEN_ADDRESS = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' --UNI contract address

AND DATE_TRUNC('day', HOUR) >= '2023-01-01'

GROUP BY period

),

btc_prices AS (

SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS period,

AVG(ASSET_USD) AS avg_btc_price

FROM thorchain.price.fact_prices

WHERE POOL_NAME = 'BTC.BTC'

AND DATE_TRUNC('day', BLOCK_TIMESTAMP) >= '2023-01-01'

GROUP BY period

),

rune_prices AS (

SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS period,

AVG(RUNE_USD) AS avg_rune_price

FROM thorchain.price.fact_prices

WHERE POOL_NAME = 'BTC.BTC'

AND DATE_TRUNC('day', BLOCK_TIMESTAMP) >= '2023-01-01'

GROUP BY period

),

uni_returns AS (

SELECT

period,

avg_uni_price,

LAG(avg_uni_price, 1) OVER (ORDER BY period) AS prev_uni_price,

(avg_uni_price - LAG(avg_uni_price, 1) OVER (ORDER BY period)) / LAG(avg_uni_price, 1) OVER (ORDER BY period) AS uni_return