sibutasinesipo2024-04-13 02:22 PM
Updated 2024-04-14
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
›
⌄
WITH PositionEvents AS (
SELECT
POOL_NAME,
BLOCK_TIMESTAMP,
CASE
WHEN IS_ACTIVE = 'TRUE' THEN 'Opened'
WHEN IS_ACTIVE = 'FALSE' THEN 'Closed'
ELSE 'Unknown'
END AS PositionStatus,
LIQUIDITY_ADJUSTED,
CASE
WHEN PRICE_LOWER_0_1_USD = 0 OR PRICE_UPPER_0_1_USD = 0 THEN NULL
ELSE (PRICE_UPPER_0_1_USD - PRICE_LOWER_0_1_USD) / NULLIF(PRICE_LOWER_0_1_USD, 0)
END AS PriceRange
FROM
ethereum.uniswapv3.ez_positions
),
VolatilityAnalysis AS (
SELECT
POOL_NAME,
BLOCK_TIMESTAMP,
PositionStatus,
COUNT(*) OVER (PARTITION BY POOL_NAME, PositionStatus ORDER BY BLOCK_TIMESTAMP) AS CumulativeCount,
AVG(LIQUIDITY_ADJUSTED) OVER (PARTITION BY POOL_NAME ORDER BY BLOCK_TIMESTAMP) AS AvgLiquidity,
AVG(PriceRange) OVER (PARTITION BY POOL_NAME ORDER BY BLOCK_TIMESTAMP) AS AvgPriceRange
FROM
PositionEvents
)
SELECT *
FROM
VolatilityAnalysis
ORDER BY
POOL_NAME,
BLOCK_TIMESTAMP;
QueryRunArchived: QueryRun has been archived