SELECT 214748364.8 AS tokens_need_to_claim

, 248699 AS eligible_users_to_claim

), airdrop_transfers AS (

select to_date(block_timestamp) as date, from_address as address, tx_hash,

-raw_amount / power(10,18) as raw_volume, from_address as _from_address, to_address AS _to_address

-- , 'send' as tx_type

FROM optimism.core.fact_token_transfers

WHERE contract_address = '0x4200000000000000000000000000000000000042'

AND from_address != '0x0000000000000000000000000000000000000000'

UNION ALL

SELECT to_date(block_timestamp) as date, to_address as address, tx_hash,

raw_amount / power(10,18) AS raw_volume,

-- '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'

),

remaining_op as (

SELECT address, sum(raw_volume) AS remaining_optimism

FROM airdrop_transfers

GROUP BY address

), op_claimers AS (

SELECT * FROM airdrop_transfers

where _from_address = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'

AND address != '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'

), airdrop AS (

SELECT claim.address as address, claim.date as claim_date, claim.raw_volume as amount, balance.remaining_optimism,

least(100.000, 100.000 * balance.remaining_optimism / amount) AS airdrop_hold_percentage,

( CASE WHEN airdrop_hold_percentage >= 90 THEN 'Hold_airdrop'

ELSE 'Sell_Airdrop' END) as data

FROM op_claimers as claim

LEFT JOIN remaining_op as balance

ON balance.address = claim.address