SELECT

block_timestamp,

tx_hash,

tx_receiver,

tx_signer,

row_number() over (partition by tx_signer order by block_timestamp) as rank

FROM near.core.fact_transactions

WHERE TX_SUCCEEDED

SELECT

date_trunc('week', block_timestamp) as date,

project_name,

label_type as sector,

count(DISTINCT tx_signer) as "Active Users",

count(DISTINCT case when rank = 1 then tx_signer end) as "New Users",

CASE when date BETWEEN '2023-01-01' and '2023-04-01' then 'Q1 2023'

when date BETWEEN '2023-04-01' and '2023-07-01' then 'Q2 2023'

when date BETWEEN '2023-07-01' and '2023-10-01' then 'Q3 2023'

when date BETWEEN '2023-10-01' and '2024-01-01' then 'Q4 2023'

end as quarter

FROM all_user a

LEFT JOIN near.core.dim_address_labels b

on a.tx_RECEIVER = b.address

WHERE date BETWEEN '2023-01-01' AND '2023-12-31'

AND project_name is not null

AND sector not in ('token', 'chadmin')

GROUP by 1 , 2 , 3

qualify row_number()over (partition by date order by "Active Users" DESC) <= 10

ORDER BY 1 DESC