with

t1 as (

select

BLOCK_TIMESTAMP as claim_date,

TO_ADDRESS as airdropers,

RAW_AMOUNT/1e18 as amount

from arbitrum.core.fact_token_transfers

where contract_address = lower('0x912ce59144191c1204e64559fe8253a0e49e6548')

and FROM_ADDRESS = lower('0x67a24CE4321aB3aF51c2D0a4801c3E111D88C9d9')

),

t2 as (

SELECT

from_address,

min(block_timestamp) as mint_date

FROM arbitrum.core.fact_transactions

GROUP by 1

HAVING mint_date > '2023-03-22'

)

SELECT

trunc(block_timestamp,'day') as date,

'Airdrop user' as type,

count(DISTINCT from_address) as users,

count(DISTINCT tx_hash) as actions

FROM arbitrum.core.fact_transactions

LEFT outer JOIN arbitrum.core.dim_labels

on address = to_address

WHERE from_address IN (SELECT airdropers FROM t1)

AND block_timestamp > '2023-03-22'

AND block_timestamp < current_date()

AND not project_name LIKE 'arbitrum foundation'

AND label_subtype LIKE 'bridge'

GROUP BY 1,2