MLDZMN2024-02-16 03:47 AM
Updated 2024-02-23
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 t1 as (select
BLOCK_TIMESTAMP,
symbol,
CURRENT_BAL
from ethereum.core.ez_balance_deltas
where USER_ADDRESS = lower('0xc977CBadD359aE06b236D9581e37fd5A03E54b84')
and symbol in ('PDT','PRIME','stETH','USDC')
),
final as (SELECT
t1.BLOCK_TIMESTAMP::date as day,
t1.symbol,
t1.CURRENT_BAL
FROM t1
INNER JOIN
(
SELECT DATE(BLOCK_TIMESTAMP) AS trade_date,t1.symbol, MAX(BLOCK_TIMESTAMP) AS max_BLOCK_TIMESTAMP
FROM t1
GROUP BY DATE(BLOCK_TIMESTAMP),t1.symbol
) t2
ON t2.trade_date = DATE(t1.BLOCK_TIMESTAMP) AND
t2.max_BLOCK_TIMESTAMP = t1.BLOCK_TIMESTAMP
and t1.symbol=t2.symbol
ORDER BY
t1.BLOCK_TIMESTAMP),
final2 as (select
date(HOUR)as day,
p.symbol,
avg(price) as token_price,
CURRENT_BAL,
CURRENT_BAL*token_price as balance_usd
QueryRunArchived: QueryRun has been archived