Yousefi_1994Optimism Airdrop - Top Holders (Without Transfer)
    Updated 2022-09-29
    with optimism_airdrop_claim_transactions as (
    select
    block_timestamp,
    event_inputs:recipient as address,
    event_inputs:amount as op_amount_claimed
    from optimism.core.fact_event_logs
    where origin_to_address = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'
    and contract_address = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'
    and event_name = 'Claimed'
    and tx_status = 'SUCCESS'
    and origin_function_signature = '0x2e7ba6ef'
    and block_timestamp::date <= '2022-09-27'
    ),
    all_claim_overview as (
    select
    count(distinct address) as number_of_user,
    sum(op_amount_claimed/1e18) as total_amount
    from optimism_airdrop_claim_transactions
    ),
    optimism_airdrop_transfer as (
    select
    transfer.tx_hash,
    transfer.block_timestamp,
    transfer.origin_from_address,
    transfer.origin_to_address,
    transfer.raw_amount/1e18 as transfer_amount,
    claim.op_amount_claimed/1e18 as claim_amount
    from optimism.core.fact_token_transfers transfer
    join optimism_airdrop_claim_transactions claim
    on transfer.origin_from_address = claim.address
    and transfer.block_timestamp >= claim.block_timestamp
    where transfer.contract_address = '0x4200000000000000000000000000000000000042'
    and transfer.origin_from_address = transfer.from_address
    and transfer.raw_amount > 0
    and transfer.raw_amount is not null
    ),
    Run a query to Download Data