BLOCK_TIMESTAMP,

ORIGIN_FROM_ADDRESS as users

from ethereum.core.fact_token_transfers

where ORIGIN_TO_ADDRESS = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1'

),

tb2 as (

select

x.BLOCK_TIMESTAMP,

x.FROM_ADDRESS,

x.TO_ADDRESS,

x.tx_hash,

ROW_NUMBER() OVER (partition by FROM_ADDRESS order by x.BLOCK_TIMESTAMP) as t_n

from optimism.core.fact_token_transfers x

join tb1 y on x.from_address = y.users and x.block_timestamp>y.block_timestamp

and from_address != '0x0000000000000000000000000000000000000000'

order by 1

)

BLOCK_TIMESTAMP::date as day,

LABEL_TYPE,

count(distinct FROM_ADDRESS) as users,

count(tx_hash) as count_txn,

-- sum(users) over (partition by LABEL_TYPE order by day) as cum_users,

sum(count_txn) over (partition by LABEL_TYPE order by day) as cum_txn

from tb2 q join optimism.core.dim_labels w on q.TO_ADDRESS= w.address

where LABEL_TYPE not in ('chadmin','operator','token')

and t_n=1 and day>=CURRENT_DATE-30

group by 1,2