-- Step 1: Collect the weekly average prices of BTC and RUNE and compute r_final (Price of BTC / Price of RUNE)

SELECT

DATE_TRUNC('week', DAY) AS week,

AVG(RUNE_PRICE_USD) AS avg_rune_price,

AVG(ASSET_PRICE_USD) AS avg_btc_price,

-- Compute r_final: Price of BTC / Price of RUNE

(AVG(ASSET_PRICE_USD) / AVG(RUNE_PRICE_USD)) AS r_final

FROM thorchain.defi.fact_daily_pool_stats

WHERE POOL_NAME = 'BTC.BTC'

AND DAY >= '2022-01-01'

GROUP BY week

),

liquidity_additions AS (

-- Step 2: Collect all add_liquidity events between January 2024 and today, and compute r_initial for each event

SELECT

tx_id,

from_address,

pool_name,

asset_amount,

rune_amount,

asset_amount_usd,

rune_amount_usd,

block_timestamp,

-- Week of the event

DATE_TRUNC('week', block_timestamp) AS event_week,

-- Initial price of BTC and RUNE at the time of liquidity addition

(asset_amount_usd / asset_amount) AS initial_btc_price,

(rune_amount_usd / rune_amount) AS initial_rune_price,

-- Compute r_initial: Initial Price of BTC / Initial Price of RUNE

((asset_amount_usd / asset_amount) / (rune_amount_usd / rune_amount)) AS r_initial

FROM thorchain.defi.fact_liquidity_actions

WHERE LP_ACTION = 'add_liquidity'

AND RUNE_AMOUNT > '1'

AND ASSET_AMOUNT_USD > '5'