SELECT DISTINCT nft_to_address, project_name, block_timestamp, tx_hash

FROM aptos.nft.ez_nft_mints

UNION

SELECT DISTINCT buyer_address AS nft_to_address, project_name, block_timestamp, tx_hash

FROM aptos.nft.ez_nft_sales

),

daus as (

SELECT

distinct sender as users,

trunc(block_timestamp,'week') as weeks,

count(distinct trunc(block_timestamp,'day')) as active_days

from aptos.core.fact_transactions

group by 1,2

having active_days>=4

),

new_users AS (

SELECT

distinct nft_to_address,

project_name,

min(TRUNC(block_timestamp, 'week')) AS date

FROM nfts where nft_to_address in (select DISTINCT users from daus) and project_name is not null

GROUP BY 1, 2

SELECT

date,

project_name,

new_users,

sum(new_users) over (partition by project_name order by date) as total_users,

ranks

FROM (

SELECT

date,

project_name,

count(distinct nft_to_address) as new_users,

RANK() OVER (PARTITION BY date ORDER BY new_users DESC) AS ranks