kaiblade2023-06-27 10:32 PM
Updated 2023-06-29
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 escrow_merge_txns AS
(SELECT *, decoded_log:accountToMerge AS account_to_merge,
decoded_log:escrowAmountMerged::INTEGER * 1e-18 AS merged_escrow_amount
FROM ethereum.core.fact_decoded_event_logs
WHERE event_name = 'AccountMerged'
AND contract_address = '0xac86855865cbf31c8f9fbb68c749ad5bd72802e3'
),
sds_price_feeds AS
(SELECT block_number, block_timestamp, tx_hash, decoded_log:current *1e-27 AS sds_price
FROM ethereum.core.ez_decoded_event_logs
WHERE contract_address = '0xc7bb32a4951600fbac701589c73e219b26ca2dfc'
AND event_name = 'AnswerUpdated'
AND block_timestamp::DATE >= '2022-01-01'
ORDER BY block_timestamp DESC),
escrow_merge_balance AS
(SELECT esc.block_number, esc.block_timestamp, esc.tx_hash, esc.event_name, 0 AS "Minted Amount", esc.merged_escrow_amount AS "Merged Escrow Amount", esc.account_to_merge AS "Wallet Address",
bal.current_bal_unadj * 1e-18 AS "SNX Balance", 0 AS "Escrowed SNX Balance", 0 AS "SDS Balance"
FROM escrow_merge_txns esc
JOIN ethereum.core.ez_balance_deltas bal
ON esc.account_to_merge = bal.user_address
AND esc.block_timestamp >= bal.block_timestamp
WHERE bal.user_address IN (SELECT account_to_merge FROM escrow_merge_txns)
AND (bal.symbol IN ('SNX', 'HAV')
OR bal.contract_address = '0xc011a72400e58ecd99ee497cf89e3775d4bd732f')
QUALIFY ROW_NUMBER() OVER (PARTITION BY esc.account_to_merge ORDER BY bal.block_timestamp DESC)=1
),
add_sdsprice_escrow_merge AS
(SELECT bal.*, sdsprice.sds_price
FROM escrow_merge_balance bal
JOIN sds_price_feeds sdsprice
ON bal.block_timestamp >= sdsprice.block_timestamp
QUALIFY ROW_NUMBER() OVER (PARTITION BY bal."Wallet Address", bal.tx_hash ORDER BY sdsprice.block_timestamp DESC)=1
)
Run a query to Download Data