AurelianLabsLVR Framework for Pool - WETH/USDC
Updated 2024-09-03
999
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
›
⌄
-- Target One Pool first, (WETH - USDC/USDT?)
-- 1. Daily Volume
-- 2. Liquidity Information
-- For single trade, we can take LVR = a(p - q)
-- And also LVR = σ²/8
-- So, we need to get few stuffs
-- a, Quantity of asset traded (in terms of what?) -> Get from blockchain, SQL
-- p, Market/CEX Price -> Get from ??? API? Minutes, Hours, or Daily?
-- q, AMM Price -> Get from blockchain, SQL
-- σ, Volatility -> Further derivation is needed
-- Volatility
-- Importantly, here we are getting HISTORICAL/Realized volatility, not Implied Volatility (future).
-- 1. Take daily closing price of the asset.
-- 2. Calculate Percentage change from one day to the next.
-- 3. Daily Return (%) = (Todays Closing Price - Yesterday Closing price) / Yesterday Closing Price * 100%
-- 4. Calculate average daily return
-- 5. Calculate standard deviation of daily return -> This is the volatility.
WITH hourly_ethereum_and_bitcoin_price as (
SELECT
asset_id,
hour,
DATE_PART('hour', hour) as hourly,
open,
high,
low,
close,
COALESCE(LAG(close) OVER (partition by asset_id ORDER BY hour ASC), close) as previous_close
FROM crosschain.price.fact_prices_ohlc_hourly
WHERE 0=0
AND (asset_id = 'ethereum' OR asset_id = 'bitcoin')
AND provider = 'coingecko'
AND date(hour) >= '2024-06-01'
QueryRunArchived: QueryRun has been archived