-- forked from new users over time by contract type @ https://flipsidecrypto.xyz/edit/queries/8a60746e-200c-4709-8161-d32bc3950b7d

with New_users as (

select TX_FROM ,

min (BLOCK_TIMESTAMP) ::date min_date

from sei.core.fact_transactions

group by 1 having min_date >= current_date - 30

),

transactions as (

select TX_ID , TX_FROM

from sei.core.fact_transactions

where TX_FROM in (select TX_FROM from New_users )

),

labels as (

select ADDRESS ,

LABEL,

LABEL_TYPE

from sei.core.dim_labels

),

msg as (

select ATTRIBUTE_VALUE ADDRESS , *

from sei.core.fact_msg_attributes join transactions using (tx_id)

where ATTRIBUTE_VALUE in (select ADDRESS from labels )

and BLOCK_TIMESTAMP::date >= current_date - 30

select BLOCK_TIMESTAMP ::date daily ,

LABEL ,

count (DISTINCT tx_id) num_transactions ,

count (DISTINCT tx_from ) num_users ,

sum (num_transactions) over (partition by LABEL order by daily) cum_transactions

from msg join labels using (ADDRESS)

group by 1, 2