t1 as (

select trunc(block_timestamp,'day') as date, tx_hash, EVENT_INPUTS:recipient as claimer, EVENT_INPUTS:amount/1e18 as op_amount

from optimism.core.fact_event_logs

where EVENT_NAME = 'Claimed'

and CONTRACT_ADDRESS = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'

and date < CURRENT_DATE

),

t2 as (

select

date, count(distinct claimer) as users, sum(op_amount) as amount,

sum(users) over (order by date) as total_claimers,

avg(248699) as available_claimers,

(total_claimers/available_claimers)*100 as pcg_users_claimed,

sum(amount) over (order by date) as total_op_claimed,

avg(4294967296) as total_supply,

total_supply*5/100 as airdrop_allocation,

(total_op_claimed/airdrop_allocation)*100 as pcg_claimed

from t1

group by 1

order by 1 desc

)

select * from t2 where date=CURRENT_DATE-1