zakkisyedCalculate ETH Deposited Accurately
Updated 2024-05-21
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 eth_swapped AS (
SELECT
CASE
WHEN LOWER(decoded_log:toAsset) = LOWER('0xae7ab96520de3a18e5e111b5eaab095312d7fe84') THEN 'stETH'
WHEN LOWER(decoded_log:toAsset) = LOWER('0xac3e018457b222d93114458476f3e3416abbe38f') THEN 'sfrxETH'
WHEN LOWER(decoded_log:toAsset) = LOWER('0xa35b1b31ce002fbf2058d22f30f95d405200a15b') THEN 'ETHx'
ELSE 'Unknown Asset'
END AS asset,
SUM(decoded_log:ethAmount / POW(10, 18)) AS ETHDeposited
FROM
ethereum.core.ez_decoded_event_logs
WHERE
event_name = 'ETHSwappedForLST'
AND LOWER(contract_address) = LOWER('0x036676389e48133b63a802f8635ad39e752d375d')
AND LOWER(origin_to_address) = LOWER('0xcbcdd778aa25476f203814214dd3e9b9c46829a1')
GROUP BY
asset
)
-- Combine all queries for different types of ETH deposited
SELECT
'L2 ETH Redeposited' AS type,
SUM(value) AS ETHdeposited
FROM
ethereum.core.fact_transactions
WHERE
tx_hash IN (
SELECT DISTINCT tx_hash
FROM ethereum.core.ez_decoded_event_logs
WHERE
event_name = 'ETHDeposit'
AND LOWER(contract_address) = LOWER('0x036676389e48133b63a802f8635ad39e752d375d')
AND LOWER(decoded_log:depositor) IN ('0x3924a9a1706285f5e92486dc19945e43fb2f98cf', '0x7aad74b7f0d60d5867b59dbd377a71783425af47')
AND tx_status = 'SUCCESS'
)
GROUP BY
Auto-refreshes every 1 hour
QueryRunArchived: QueryRun has been archived