with projectsTb as ( select address, Initcap(project_name) as project, label_type from near.core.dim_address_labels where project_name is not null and label_type not in ('chadmin', 'cex', 'token', 'fungible_token') and project_name != 'Exchanges' AND NOT project_name ilike any ('%Circle%', '%Tether%') -- AND project != 'Tether (Usdt)' ), new_usersTb as ( select tx_signer, min(block_timestamp) as onboarding_timestamp from near.core.fact_transactions where tx_signer not in ( select address from projectsTb ) and TX_SUCCEEDED = 'True' --qualify rank = 1 GROUP BY 1 ) , top10_procets_new_usr as ( select project, count(distinct t.tx_signer) as projects_new_usrs, ROW_NUMBER() over (ORDER by projects_new_usrs DESC) as rank FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp ) JOIN projectsTb p on t.TX_RECEIVER = p.address GROUP BY 1 qualify rank <= 10 ORDER by rank ) , first_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 1) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , second_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 2) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , third_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 3) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , fourth_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 4) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , fifth_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 5) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , sixth_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 6) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , seventh_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 7) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , eight_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 8) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , ninth_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 9) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , tenth_top_proj as ( SELECT project as project0, count(DISTINCT t.tx_signer) as first_month_new_usrs FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr WHERE rank = 10) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND monthname(BLOCK_TIMESTAMP::date) = monthname('{{Since_date}}') GROUP BY 1 ) , top_proj_over_time as ( SELECT date_trunc('month', BLOCK_TIMESTAMP)::date as date, project, count(DISTINCT t.tx_signer) as new_usrs_over_time FROM near.core.fact_transactions t JOIN new_usersTb u on ( t.tx_signer = u.tx_signer and t.BLOCK_TIMESTAMP = u.onboarding_timestamp) JOIN projectsTb p on t.TX_RECEIVER = p.address WHERE project in (SELECT project FROM top10_procets_new_usr) AND BLOCK_TIMESTAMP >= '{{Since_date}}' AND month(BLOCK_TIMESTAMP::date) = month(BLOCK_TIMESTAMP::date) --'Jan' GROUP BY 1,2 ) SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join first_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join second_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join third_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join fourth_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join fifth_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join sixth_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join seventh_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join eight_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join ninth_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) UNION SELECT date, project, round(((new_usrs_over_time - first_month_new_usrs)/first_month_new_usrs) * 100, 2) as "% New Usrs Change" FROM top_proj_over_time left outer join tenth_top_proj ON project0 = project WHERE date >= '{{Since_date}}' AND month(date) <> month(current_date) --*/

Top Projects on Near