charliemarketplaceARB Claimers Categorized
    Updated 2023-04-21
    -- forked from ARB_CLAIM_CATEGORIES @ https://flipsidecrypto.xyz/edit/queries/86fafe5e-c14a-484c-a3bc-7b7d3d01e20f
    -- forked from failed_claims @ https://flipsidecrypto.xyz/edit/queries/851dec24-3155-485b-971b-9bdca321019b

    -- Users that successfully claimed ARB from the main contract
    with successful_claims AS (
    SELECT
    TO_ADDRESS as recipient,
    SYMBOL,
    SUM(AMOUNT) as amount_claimed
    FROM arbitrum.core.ez_token_transfers
    WHERE CONTRACT_ADDRESS = LOWER('0x912CE59144191C1204E64559FE8253a0e49E6548')
    AND FROM_ADDRESS = '0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9'
    GROUP BY recipient, symbol
    ),

    -- getting all these claimers ARB transfers
    claimer_transfers AS (
    SELECT
    att.BLOCK_NUMBER,
    att.BLOCK_TIMESTAMP,
    att.TX_HASH,
    FROM_ADDRESS as claimer,
    amount_claimed,
    att.TO_ADDRESS as drop_receiver,
    COALESCE(AMOUNT,0) as amount_sent
    FROM
    arbitrum.core.ez_token_transfers att RIGHT JOIN successful_claims sc ON
    att.FROM_ADDRESS = sc.recipient
    WHERE contract_address = LOWER('0x912CE59144191C1204E64559FE8253a0e49E6548')
    ),

    claimer_receives AS (
    SELECT
    att.BLOCK_NUMBER,
    att.BLOCK_TIMESTAMP,
    att.TX_HASH,