SELECT

from_address as wallet

, block_timestamp::date as ddate

from ethereum.core.fact_transactions

WHERE 1=1

AND block_timestamp between '{{Start_date}}' AND '{{End_date}}'

AND nonce=0

AND STATUS = 'SUCCESS'

,

txns as (

SELECT

t.origin_from_address as wallet

, l.label_type

, min(t.block_timestamp) as min_dtime

from ethereum.core.fact_event_logs t

LEFT JOIN ethereum.core.dim_labels l on t.contract_address = l.address

INNER JOIN new_wallets n on n.wallet = t.origin_from_address

WHERE 1=1

AND t.block_timestamp between '{{Start_date}}' AND '{{End_date}}'

-- AND from_address IN (SELECT wallet from new_wallets)

AND l.LABEL_TYPE IS NOT NULL

AND l.LABEL_TYPE NOT IN ('token', 'cex', 'chadmin', 'operator','flotsam')

AND l.LABEL NOT IN ('Weth', 'weth')

-- AND STATUS = 'SUCCESS'

GROUP BY 1,2

,

refine as (

SELECT

*

, row_number() OVER(PARTITION BY wallet ORDER BY min_dtime ASC) as num

from txns