SELECT * FROM ethereum.core.fact_transactions c

WHERE EXISTS (

SELECT * FROM ethereum.core.fact_event_logs d

WHERE c.TX_HASH = d.TX_HASH

AND CONTRACT_ADDRESS IN (SELECT ADDRESS FROM ethereum.core.dim_contracts WHERE NAME ilike '%airdrop%')

)

),

LIST2 AS (

SELECT * FROM ethereum.core.fact_transactions a

WHERE EXISTS (

SELECT BYTES_SIGNATURE FROM

ethereum.core.dim_function_signatures b

WHERE text_signature ilike '%airdrop%'

AND b.bytes_signature = a.origin_function_signature

)),

LIST3 AS (

SELECT distinct(FROM_ADDRESS) AS WALLET_ADDRESS FROM LIST1

UNION

SELECT distinct(TO_ADDRESS) AS WALLET_ADDRESS FROM LIST1

UNION

SELECT distinct(FROM_ADDRESS) AS WALLET_ADDRESS FROM LIST2

UNION

SELECT distinct(TO_ADDRESS) AS WALLET_ADDRESS FROM LIST2

SELECT COUNT(DISTINCT(WALLET_ADDRESS)) AS TOTAL_WALLET_ADDRESS_NUMBER FROM LIST3