with drops as (

select

to_address as address,

min(block_timestamp) as airdrop_time,

sum(raw_amount / pow(10,18)) as amount_dropped

FROM

optimism.core.fact_token_transfers

where block_timestamp > '2023-09-18'

and from_address = lower('0x2501c477D0A35545a387Aa4A3EEe4292A9a8B3F0')

and contract_address = lower('0x4200000000000000000000000000000000000042')

group by address

,sends as (

SELECT

ttx.tx_hash,

ttx.from_address,

ttx.to_address,

ttx.raw_amount / pow(10,18) as amount_sent,

--con.symbol,

coalesce(labs.project_name,'unknown') as project

FROM

optimism.core.fact_token_transfers ttx

join drops dr on dr.address = ttx.from_address

left join optimism.core.dim_labels labs

on labs.address = ttx.to_address

where ttx.block_timestamp > '2023-09-18'

and ttx.contract_address = '0x4200000000000000000000000000000000000042'

and ttx.block_timestamp > dr.airdrop_time

),

delegations as (

select

'delegation' as destination,

sum(amount) as amount_delegated,

count(distinct(address)) as n_addresses