Ramahar$OP airdrop user behavior
    Updated 2022-11-13
    /* Query referring to https://app.flipsidecrypto.com/dashboard/op-airdrop-NMHf05 submision */
    /*Ammended accordingly*/
    WITH claimers AS ( SELECT
    to_address AS address,
    SUM(raw_amount) / POW(10, 18) AS amount
    FROM optimism.core.fact_token_transfers
    WHERE from_address = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de' AND contract_address = '0x4200000000000000000000000000000000000042' AND origin_function_signature = '0x2e7ba6ef'
    GROUP BY 1
    ),
    receivers AS ( SELECT
    claimers.address,
    SUM(transfers.raw_amount) / POW(10, 18) AS amount
    FROM optimism.core.fact_token_transfers transfers
    JOIN claimers
    ON transfers.to_address = claimers.address
    WHERE contract_address = '0x4200000000000000000000000000000000000042'
    GROUP BY 1
    ),
    senders AS ( SELECT
    claimers.address,
    SUM(transfers.raw_amount) / POW(10, 18) AS amount
    FROM optimism.core.fact_token_transfers transfers
    JOIN claimers
    ON transfers.from_address = claimers.address
    WHERE contract_address = '0x4200000000000000000000000000000000000042'
    GROUP BY 1
    ),
    holders AS (
    SELECT
    claimers.address,
    claimers.amount AS claimed,
    receivers.amount AS received,
    senders.amount AS sent,
    (receivers.amount - ZEROIFNULL(senders.amount)) AS holdings,
    CASE
    WHEN holdings > claimed THEN 'Accumulators'
    Run a query to Download Data