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

),