WITH GENESIS_BLOCKS AS ( SELECT CONTRACT_ADDRESS, min(BLOCK_NUMBER) as genesis_block FROM ethereum.core.ez_token_transfers GROUP BY 1 HAVING min(BLOCK_NUMBER) >= 17046105 AND min(BLOCK_NUMBER) < 17046105 + 344000 ) , FIRST_SENDERS AS ( SELECT first_transfer.CONTRACT_ADDRESS, CASE WHEN first_sender_block - g.genesis_block <= 10000 THEN 1 WHEN first_sender_block - g.genesis_block > 10000 AND first_sender_block - g.genesis_block <= 100000 THEN 2 WHEN first_sender_block - g.genesis_block > 100000 AND first_sender_block - g.genesis_block <= 500000 THEN 3 WHEN first_sender_block - g.genesis_block > 500000 AND first_sender_block - g.genesis_block <= 1000000 THEN 4 WHEN first_sender_block - g.genesis_block > 1000000 THEN 5 ELSE NULL END as token_age, COUNT(DISTINCT FROM_ADDRESS) AS NEW_SENDERS FROM ( SELECT CONTRACT_ADDRESS , FROM_ADDRESS , MIN(BLOCK_NUMBER) as first_sender_block FROM ethereum.core.ez_token_transfers WHERE BLOCK_NUMBER >= 17046105 AND BLOCK_NUMBER < 17046105 + 344000 GROUP BY 1,2 ) first_transfer JOIN GENESIS_BLOCKS g on g.contract_address = first_transfer.contract_address GROUP BY 1,2 ) , FIRST_RECEIVERS AS ( SELECT first_transfer.CONTRACT_ADDRESS, CASE WHEN first_receiver_block - g.genesis_block <= 10000 THEN 1 WHEN first_receiver_block - g.genesis_block > 10000 AND first_receiver_block - g.genesis_block <= 100000 THEN 2 WHEN first_receiver_block - g.genesis_block > 100000 AND first_receiver_block - g.genesis_block <= 500000 THEN 3 WHEN first_receiver_block - g.genesis_block > 500000 AND first_receiver_block - g.genesis_block <= 1000000 THEN 4 WHEN first_receiver_block - g.genesis_block > 1000000 THEN 5 ELSE NULL END as token_age, COUNT(DISTINCT TO_ADDRESS) AS NEW_RECIPIENTS FROM ( SELECT CONTRACT_ADDRESS , TO_ADDRESS , MIN(BLOCK_NUMBER) as first_receiver_block FROM ethereum.core.ez_token_transfers WHERE BLOCK_NUMBER >= 17046105 AND BLOCK_NUMBER < 17046105 + 344000 GROUP BY 1,2 ) first_transfer JOIN GENESIS_BLOCKS g on g.contract_address = first_transfer.contract_address GROUP BY 1,2 ) , WALLET_AGES AS ( SELECT TO_ADDRESS AS ADDRESS, MIN(BLOCK_NUMBER) AS FIRST_SEEN_BLOCK FROM ethereum.core.fact_transactions GROUP BY 1 ) , TRANSACTIONS AS ( SELECT t.CONTRACT_ADDRESS, g.genesis_block, CASE WHEN t.BLOCK_NUMBER - g.genesis_block <= 10000 THEN 1 WHEN t.BLOCK_NUMBER - g.genesis_block > 10000 AND t.BLOCK_NUMBER - g.genesis_block <= 100000 THEN 2 WHEN t.BLOCK_NUMBER - g.genesis_block > 100000 AND t.BLOCK_NUMBER - g.genesis_block <= 500000 THEN 3 WHEN t.BLOCK_NUMBER - g.genesis_block > 500000 AND t.BLOCK_NUMBER - g.genesis_block <= 1000000 THEN 4 WHEN t.BLOCK_NUMBER - g.genesis_block > 1000000 THEN 5 ELSE NULL END as token_age , COUNT(DISTINCT t.TX_HASH) AS TX_COUNT , COUNT(DISTINCT t._LOG_ID) AS TRANSFERS_COUNT , AVG(t.BLOCK_NUMBER - _from_ages.FIRST_SEEN_BLOCK) as avg_sender_age , AVG(t.BLOCK_NUMBER - _to_ages.FIRST_SEEN_BLOCK) as avg_receiver_age , STDDEV_POP(raw_amount) / NULLIF(avg(raw_amount),0) as stdev_amount_vs_avg , COUNT(DISTINCT t.BLOCK_NUMBER) as no_active_blocks FROM ethereum.core.ez_token_transfers t JOIN GENESIS_BLOCKS g on g.contract_address = t.contract_address JOIN WALLET_AGES _from_ages on _from_ages.address = t.FROM_ADDRESS JOIN WALLET_AGES _to_ages on _to_ages.address = t.TO_ADDRESS WHERE t.BLOCK_NUMBER >= 17046105 --prev 4M AND t.BLOCK_NUMBER < 17046105 + 344000 GROUP BY 1,2,3 ) SELECT t.CONTRACT_ADDRESS , t.genesis_block , t.token_age , fr.NEW_RECIPIENTS , fs.NEW_SENDERS , t.TX_COUNT , t.TRANSFERS_COUNT , t.TRANSFERS_COUNT / NULLIF(fs.NEW_SENDERS, 0) AS TRANSFERS_PER_NEW_SENDER , t.TRANSFERS_COUNT / NULLIF(fr.NEW_RECIPIENTS, 0) AS TRANSFERS_PER_NEW_RECEIVER , t.avg_sender_age , t.avg_receiver_age , stdev_amount_vs_avg , TRANSFERS_COUNT / NULLIF(t.no_active_blocks,0) AS avg_trfrs_per_block FROM TRANSACTIONS t JOIN FIRST_SENDERS fs on fs.contract_address = t.contract_address AND fs.token_age = t.token_age JOIN FIRST_RECEIVERS fr on fr.contract_address = t.contract_address AND fr.token_age = t.token_age WHERE t.genesis_block < 17046105 + 344000 AND t.genesis_block >= 17046105 ORDER BY t.genesis_block DESC

Query	Creator	Created
query-update	masandri	Jun 30, 2023
modified_leo_query ethereum evm	masandri	Jun 30, 2023
exp ethereum evm	masandri	Jun 30, 2023
original	masandri	Jun 30, 2023
Getting Started	masandri	Jun 30, 2023

masandri