elvisOP Price action Analysis, T5: velodrome OP flows and balance
Updated 2022-12-24Copy Reference Fork
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
›
⌄
WITH
OP_price_table as (
SELECT 'OP_price' as provider, hour as block_timestamp, symbol, price price_OP
FROM optimism.core.fact_hourly_token_prices
WHERE hour > '2022-10-01'
AND SYMBOL ='OP'
),
velo_op_swaps_in AS (
SELECT block_timestamp::date date, sum(amount_in) as OP_IN
FROM optimism.velodrome.ez_swaps
WHERE symbol_in = 'OP'
AND block_timestamp > '2022-10-01'
GROUP BY date
),
velo_op_swaps_out AS (
SELECT block_timestamp::date date, sum(amount_out) as OP_OUT
FROM optimism.velodrome.ez_swaps
WHERE symbol_out = 'OP'
AND block_timestamp > '2022-10-01'
GROUP BY date
),
velo_op_swaps_t AS (
SELECT date, OP_IN, -OP_out OP_out, sum(OP_in-op_out) OVER (ORDER BY date rows between unbounded preceding and current row) OP_locked_change
FROM velo_op_swaps_in FULL OUTER JOIN velo_op_swaps_out USING (date)
),
velo_op_swaps AS (
SELECT *
FROM OP_price_table P LEFT JOIN velo_op_swaps_t S ON P.block_timestamp::date = S.date
)
SELECT *
FROM velo_op_swaps
Run a query to Download Data