Alexayhold
    Updated 2022-09-25
    WITH op_airdrop_1_metadata AS ( SELECT 214748364.8 AS op_tokens_to_claim , 248699 AS eligible_users),

    op_transfers AS ( SELECT block_timestamp::date AS utc_date , tx_hash , from_address AS wallet , -raw_amount / power(10,18) AS amount
    , 'send' AS tx_type , from_address AS _from_address , to_address AS _to_address
    FROM optimism.core.fact_token_transfers
    WHERE contract_address = '0x4200000000000000000000000000000000000042'
    AND from_address != '0x0000000000000000000000000000000000000000'
    UNION ALL
    SELECT block_timestamp::date AS utc_date, tx_hash , to_address AS wallet , raw_amount / power(10,18) AS amount
    , 'receive' AS tx_type , from_address AS _from_address , to_address AS _to_address
    FROM optimism.core.fact_token_transfers
    WHERE contract_address = '0x4200000000000000000000000000000000000042'
    AND to_address != '0x0000000000000000000000000000000000000000'),

    op_balances AS ( SELECT wallet , greatest(0, sum(amount)) AS balance
    FROM op_transfers
    GROUP BY 1),

    op_airdrop_claims AS ( SELECT *
    FROM op_transfers
    WHERE _from_address = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'
    AND wallet != '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'),

    op_claimers AS ( SELECT c.wallet, c.amount AS claimed_amount, b.balance, least(100.000, 100.000 * b.balance / claimed_amount) AS perc_airdrop_held,
    (CASE WHEN perc_airdrop_held >= 90 THEN 'Hold'
    WHEN perc_airdrop_held >= 10 THEN 'Hold (Partial)'
    ELSE 'Sold' END) AS _group
    FROM op_airdrop_claims AS c
    LEFT JOIN op_balances AS b
    ON b.wallet = c.wallet)


    SELECT _group, count(*) AS wallets
    FROM op_claimers
    Run a query to Download Data