angeal2023-06-23 10:15 AM
    Updated 2023-07-23
    WITH recipients AS (select
    block_timestamp as claim_time,
    to_address as recipient,
    amount AS airdrop_amount
    from
    optimism.core.ez_token_transfers
    where
    contract_address = lower('0x4200000000000000000000000000000000000042') --OP token
    AND from_address = lower('0xfedfaf1a10335448b7fa0268f56d2{{param_5Yh1}}b44dbd357de') --claim contract
    AND block_timestamp > '2023-01-01'),

    swaps AS (
    SELECT
    sender AS airdrop_recipient,
    sum(amount_in) AS airdrop_swapped
    FROM
    optimism.core.ez_dex_swaps ds
    JOIN recipients rr ON ds.sender = rr.recipient
    WHERE
    token_in = lower('0x4200000000000000000000000000000000000042')
    AND
    ds.block_timestamp > rr.claim_time
    GROUP BY sender
    )

    SELECT
    recipient,
    airdrop_amount,
    airdrop_swapped,
    IFF(airdrop_swapped > airdrop_amount, 1, coalesce(airdrop_swapped, 0)/airdrop_amount) AS prop_swapped
    FROM
    recipients r
    LEFT JOIN swaps s ON r.recipient = s.airdrop_recipient



    Run a query to Download Data