rmasVelodrome Health - Prep Query
Updated 2022-08-17Copy 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
34
35
36
›
⌄
WITH
lp_actions AS (
SELECT tx_hash
, block_timestamp
, pool_address
, lp_action
, (CASE WHEN lp_token_action = 'mint' THEN 1.000
WHEN lp_token_action = 'burn' THEN -1.000 END) AS sign
, lp_token_action
, lp_token_amount * sign AS lp_token_amount
, lp_token_amount_usd / lp_token_amount AS lp_token_price_usd
FROM optimism.velodrome.ez_lp_actions
WHERE block_timestamp >= '2022-06-16 00:00:00.000'
),
daily_lp_token_supply_change AS (
SELECT pool_address
, block_timestamp::date AS utc_date
, sum(lp_token_amount) AS lp_token_change
-- , avg(lp_token_price_usd) AS lp_token_price_usd
FROM lp_actions
GROUP BY 1,2
),
dim_date AS (
SELECT dateadd('day', -seq4(), CURRENT_DATE) AS utc_date
FROM TABLE(GENERATOR(rowcount => 10000))
WHERE utc_date BETWEEN '2022-06-16' AND CURRENT_DATE
),
Run a query to Download Data