WITH -- parts of code sourced from crypto_edgar

arb_airdrop AS (

SELECT

ethereum.public.udf_hex_to_int (DATA) / pow(10, 18) AS amount,

CONCAT('0x', SUBSTR(TOPICS[1], -40, 40)) AS receiver_wallet,

block_number,

block_timestamp,

tx_hash

FROM

arbitrum.core.fact_event_logs

WHERE

ORIGIN_TO_ADDRESS = '0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9'

AND ORIGIN_FUNCTION_SIGNATURE = '0xae373c1b'

AND BLOCK_NUMBER >= 70506697

AND TX_STATUS = 'SUCCESS'

),

arb_claimers AS (

SELECT

a.block_timestamp,

a.from_address,

b.amount

FROM

arbitrum.core.fact_transactions a

JOIN arb_airdrop b ON a.from_address = b.receiver_wallet

WHERE

ORIGIN_FUNCTION_SIGNATURE = '0x4e71d92d'

AND a.TO_ADDRESS = '0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9'

AND a.BLOCK_NUMBER >= 70506697

AND a.STATUS = 'SUCCESS'

),

arb_activity AS (

SELECT from_address, COUNT(*) as num_transactions,

DATEDIFF('DAY', MIN(block_timestamp), '2023-02-06'::date) as days_since_join