-- Selecting the initial and current token prices for the specified pools

SELECT

pool_address,

DATE_TRUNC('day', block_timestamp) AS timespan,

token0_price AS tok0_price,

token1_price AS tok1_price,

FIRST_VALUE(token0_price) OVER (PARTITION BY pool_address ORDER BY block_timestamp) AS started_tok0_price,

FIRST_VALUE(token1_price) OVER (PARTITION BY pool_address ORDER BY block_timestamp) AS started_tok1_price

FROM ethereum.uniswapv3.ez_lp_actions

WHERE pool_address IN (

'0xcbcdf9626bc03e24f779434178a73a0b4bad62ed', -- WBTC-WETH

'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640', -- USDC-WETH

'0x4e68ccd3e89f51c3074ca5072bbac773960dfa36' -- WETH-USDT

)

AND block_timestamp >= CURRENT_DATE - INTERVAL '30 DAY'

),

price_ratios AS (

-- Calculate the price ratio using the formula: tok1_price / started_tok1_price * started_tok0_price / tok0_price

SELECT

pool_address,

timespan,

tok0_price,

tok1_price,

started_tok0_price,

started_tok1_price,

(tok1_price / started_tok1_price) * (started_tok0_price / tok0_price) AS price_ratio

FROM tokens

),

impermanent_loss_calculations AS (

-- Calculate the impermanent loss based on the price ratio

SELECT

pool_address,

timespan,