SELECT

Address,

INITCAP(Address_name) AS Address_name,

INITCAP(label_type) AS label,

INITCAP(Project_name) AS Project_name

FROM

near.core.dim_address_labels

WHERE

label_type NOT IN ('cex', 'token')

AND Project_name IS NOT NULL

AND Project_name NOT IN ('near', 'Usdc', 'lnr')

--Date_trunc('week', nf.Block_timestamp) AS week,

npj.Project_name, -- Project information from label table

COUNT(DISTINCT nf.tx_signer) AS users

FROM

near.core.fact_transactions nf

JOIN

nearproject npj

ON nf.tx_receiver = npj.Address -- Join on transaction receiver address

WHERE

nf.block_timestamp BETWEEN '2024-01-01' AND '2024-12-31'

AND nf.tx_succeeded = 1

AND label NOT IN ('cex', 'token')

AND npj.Project_name IS NOT NULL

AND npj.Project_name NOT IN ('near', 'Usdc', 'lnr')

GROUP BY

npj.Project_name -- Group by project to aggregate fees

ORDER BY

users DESC;

LIMIT 20;