zackmendelSingle Tx LVR V3
Updated 2024-09-03
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
⌄
-- forked from Pool Specific LVR V3 @ https://flipsidecrypto.xyz/studio/queries/508bf403-af5c-474d-9bed-bcd799aefee6
-- forked from Pool Specific LVR @ https://flipsidecrypto.xyz/studio/queries/fd008048-313b-4f48-9d2c-b5372e6df8eb
/*
For discrete trades, calculate LVR using the formula: LVR = a(p - q),
where
'a' is the asset quantity traded,
'p' is the market price, and
'q' is the AMM price
*/
WITH price AS (
SELECT
hour,
token_address,
symbol,
price
FROM ethereum.price.ez_prices_hourly
),
pools AS (
SELECT
hour,
SUM(CASE WHEN token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN price ELSE 0 END) AS eth_price,
SUM(CASE WHEN token_address = '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' THEN price ELSE 0 END) AS btc_price,
SUM(CASE WHEN token_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN price ELSE 0 END) AS usdc_price,
SUM(CASE WHEN token_address = '0xdac17f958d2ee523a2206206994597c13d831ec7' THEN price ELSE 0 END) AS usdt_price
FROM price
WHERE token_address IN ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xdac17f958d2ee523a2206206994597c13d831ec7') -- WETH, WBTC, USDC, USDT
-- AND hour::date = current_date - 1
GROUP BY hour
),
pool AS (
SELECT
QueryRunArchived: QueryRun has been archived