zakkisyedLST Deposits on Kelp
    Updated 2024-05-08
    WITH target_transactions AS (
    SELECT
    DISTINCT tx_hash
    FROM
    ethereum.core.fact_decoded_event_logs
    WHERE
    LOWER(decoded_log:to) = LOWER('0x036676389e48133b63a802f8635ad39e752d375d') -- eth deposit pool
    ),

    eth_deposits AS (
    SELECT
    'ETH' AS asset,
    SUM(decoded_log:depositAmount / POW(10, 18)) AS deposit_amt
    FROM
    ethereum.core.fact_decoded_event_logs
    WHERE
    LOWER(contract_address) = LOWER('0x036676389e48133b63a802f8635ad39e752d375d') -- eth deposit pool
    AND event_name = 'ETHdeposit'
    GROUP BY 1
    ),

    asset_deposits AS (
    SELECT
    CASE
    WHEN LOWER(logs.contract_address) = LOWER('0xae7ab96520de3a18e5e111b5eaab095312d7fe84') THEN 'stETH'
    WHEN LOWER(logs.contract_address) = LOWER('0xa35b1b31ce002fbf2058d22f30f95d405200a15b') THEN 'ETHx'
    WHEN LOWER(logs.contract_address) = LOWER('0xac3e018457b222d93114458476f3e3416abbe38f') THEN 'sfrxETH'
    ELSE 'Unknown Asset'
    END AS asset,
    SUM(decoded_log:depositAmount / POW(10, 18)) AS deposit_amt
    FROM
    ethereum.core.fact_decoded_event_logs logs
    JOIN
    target_transactions tt ON logs.tx_hash = tt.tx_hash
    WHERE
    logs.event_name = 'AssetDeposit'
    QueryRunArchived: QueryRun has been archived