WITH

GENESIS_BLOCKS AS (

SELECT CONTRACT_ADDRESS, min(BLOCK_NUMBER) as genesis_block

FROM arbitrum.core.ez_token_transfers

GROUP BY 1

)

, TRANSACTIONS AS (

SELECT

t.CONTRACT_ADDRESS,

BLOCK_NUMBER,

TX_HASH,

_LOG_ID,

FROM_ADDRESS,

TO_ADDRESS,

CASE WHEN LEAD(BLOCK_NUMBER, -1, 0) OVER (PARTITION BY t.CONTRACT_ADDRESS, FROM_ADDRESS ORDER BY BLOCK_NUMBER ASC) = 0 THEN 1 ELSE 0 END AS IS_NEW_SENDER,

CASE WHEN LEAD(BLOCK_NUMBER, -1, 0) OVER (PARTITION BY t.CONTRACT_ADDRESS, TO_ADDRESS ORDER BY BLOCK_NUMBER ASC) = 0 THEN 1 ELSE 0 END AS IS_NEW_RECEIVER,

LEAD(BLOCK_NUMBER, 1, NULL) OVER (PARTITION BY t.CONTRACT_ADDRESS ORDER BY BLOCK_NUMBER ASC) - BLOCK_NUMBER AS NEXT_TX_BLOCK_DIFF,

RAW_AMOUNT

FROM arbitrum.core.ez_token_transfers t

)

, WALLET_AGES AS (

SELECT

TO_ADDRESS AS ADDRESS,

MIN(BLOCK_NUMBER) AS FIRST_SEEN_BLOCK

FROM arbitrum.core.fact_transactions

GROUP BY 1

)

SELECT

t.CONTRACT_ADDRESS,

g.genesis_block,

CASE