t1 as (

select

distinct case when tx_signer ilike '%hotwallet%' or tx_signer ilike '%relay.hot%' or tx_signer ilike '%users.kaiching%' OR

tx_signer ilike '%sweat-relayer%' or tx_signer ilike '%game.hot.tg%' or tx_signer ilike '%playember_reserve%' or tx_signer ilike '%sweat_welcome%'

or tx_signer ilike '%spin.sweat%' or tx_signer ilike '%oracle.sweat%' or tx_signer = 'tg' or tx_signer ilike '%mintbase.near%' or tx_signer ilike '%distributions.grow%'

or tx_signer ilike '%free-name.herewallet%' or tx_signer ilike '%team.herewallet%'

or tx_signer ilike '%meteor-relayer%' or tx_signer ilike '%relay.aurora%' or tx_signer ilike '%learn.sweat%' or tx_signer = 'here.tg' or tx_signer = '0here.tg' then tx_receiver else tx_signer end as tx_signer,

min(block_timestamp) as debut

from near.core.fact_transactions

where tx_succeeded = TRUE

group by 1

),

t2 as (

SELECT

distinct tx_signer,debut from t1 where debut >=CURRENT_DATE-INTERVAL '1 WEEK'

),

hourly as (

select

trunc(debut,'hour') as date,

count(distinct tx_signer) as new_users,

sum(new_users) over (order by date) as cum_new_users

from t2

group by 1

order by 1 asc

),

t3 as (

select

distinct case when tx_signer ilike '%hotwallet%' or tx_signer ilike '%relay.hot%' or tx_signer ilike '%users.kaiching%' OR

tx_signer ilike '%sweat-relayer%' or tx_signer ilike '%game.hot.tg%' or tx_signer ilike '%playember_reserve%' or tx_signer ilike '%sweat_welcome%'

or tx_signer ilike '%spin.sweat%' or tx_signer ilike '%oracle.sweat%' or tx_signer = 'tg' or tx_signer ilike '%mintbase.near%' or tx_signer ilike '%distributions.grow%'

or tx_signer ilike '%free-name.herewallet%' or tx_signer ilike '%team.herewallet%'

or tx_signer ilike '%meteor-relayer%' or tx_signer ilike '%relay.aurora%' or tx_signer ilike '%learn.sweat%' or tx_signer = 'here.tg' or tx_signer = '0here.tg' then tx_receiver else tx_signer end as tx_signer,

min(block_timestamp) as debut

from near.core.fact_transactions

where tx_succeeded = TRUE