labels as (

SELECT

address,

address_name,

project_name

from gnosis.core.dim_labels

),

transactions as (

SELECT

trunc(block_timestamp,'day') as date,

case when address_name is not null then address_name else to_address end as address,

count(distinct tx_hash) as txs,

count(distinct from_address) as active_users

from gnosis.core.fact_transactions x

left join labels y on x.to_address=y.address

where date >= '2022-09-01'

group by 1,2

having txs >1000

),

new_users as (

SELECT

distinct from_address,

min(block_timestamp) as debut

from gnosis.core.fact_transactions

group by 1

),

new_users_2 as (

SELECT

trunc(debut,'day') as date,

count(distinct from_address) as new_users

from new_users where date >='2022-09-01'

group by 1

)

SELECT

* from transactions order by 1 asc