kaiblade2023-06-27 10:32 PM
    Updated 2023-06-29
    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