SELECT

BLOCK_TIMESTAMP,

POOL_NAME,

AMOUNT_IN,

AMOUNT_OUT,

PLATFORM,

TOKEN_IN,

TOKEN_OUT,

SYMBOL_IN,

SYMBOL_OUT,

(AMOUNT_OUT / AMOUNT_IN) AS AMM_PRICE -- Calculating q

FROM arbitrum.defi.ez_dex_swaps

WHERE platform IN ('uniswap-v2')

),

market_prices AS (

SELECT

HOUR as BLOCK_TIMESTAMP,

TOKEN_ADDRESS AS TOKEN_IN,

PRICE AS MARKET_PRICE

FROM arbitrum.price.ez_prices_hourly

),

lvr_calculation AS (

SELECT

td.BLOCK_TIMESTAMP,

td.POOL_NAME,

td.AMOUNT_IN,

td.AMM_PRICE,

mp.MARKET_PRICE,

td.AMOUNT_IN * (mp.MARKET_PRICE - td.AMM_PRICE) AS LVR_DISCRETE -- Calculating LVR

FROM transaction_data td

LEFT JOIN market_prices mp

ON td.TOKEN_IN = mp.TOKEN_IN

AND ABS(TIMESTAMPDIFF(second, td.BLOCK_TIMESTAMP, mp.BLOCK_TIMESTAMP)) <= 1800 -- 30 minutes tolerance

SELECT * FROM lvr_calculation;