zakkisyedCalculate ETH Deposited Accurately
    Updated 2024-05-21
    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