-- Step 1: Get the weekly average price of RUNE and BTC

SELECT

DATE_TRUNC('week', DAY) AS week,

AVG(RUNE_PRICE_USD) AS avg_rune_price,

AVG(ASSET_PRICE_USD) AS avg_btc_price

FROM thorchain.defi.fact_daily_pool_stats

WHERE POOL_NAME = 'BTC.BTC'

AND DAY >= '2024-01-01'

GROUP BY week

),

deposit_data AS (

-- Step 2: Gather deposits made in the last 3 months

SELECT

tx_id,

from_address,

pool_name,

asset_amount,

rune_amount,

asset_amount_usd,

rune_amount_usd,

block_timestamp

FROM thorchain.defi.fact_liquidity_actions

WHERE LP_ACTION = 'add_liquidity'

AND RUNE_AMOUNT > '1'

AND ASSET_AMOUNT_USD > '5'

AND pool_name = 'BTC.BTC'

AND block_timestamp >= '2024-01-01'

AND block_timestamp >= DATEADD('month', -3, CURRENT_DATE())

),

il_computation AS (

-- Step 3: Calculate the theoretical IL for each deposit using the ratio-based formula

SELECT

dd.tx_id,