SELECT

TX_ID,

OWNER_MSG['address'] AS owner,

DATE_TRUNC('month', BLOCK_TIMESTAMP) as deployment_month,

BLOCK_TIMESTAMP,

MIN(BLOCK_TIMESTAMP) OVER (PARTITION BY OWNER_MSG['address']) as first_deployment

FROM aleo.core.fact_transactions

WHERE TX_TYPE = 'deploy'

AND TX_SUCCEEDED = 'TRUE'

),

monthly_stats AS (

SELECT

deployment_month,

COUNT(DISTINCT owner) as active_deployers,

COUNT(DISTINCT TX_ID) as successful_deployments,

COUNT(DISTINCT CASE

WHEN BLOCK_TIMESTAMP = first_deployment THEN owner

END) as new_deployers

FROM deployer_activity

GROUP BY deployment_month

SELECT

deployment_month as "Month",

active_deployers as "Monthly Active Deployers",

successful_deployments as "Deployments",

new_deployers as "New Deployers",

SUM(active_deployers) OVER (ORDER BY deployment_month) as "Cumulative Deployers",

SUM(successful_deployments) OVER (ORDER BY deployment_month) as "Total Deployments",

ROUND(successful_deployments::DECIMAL / active_deployers, 2) as "Deployments per Developer",

ROUND((active_deployers::DECIMAL /

NULLIF(LAG(active_deployers) OVER (ORDER BY deployment_month), 0) - 1) * 100, 2) as "MoM Growth %"

FROM monthly_stats

ORDER BY deployment_month DESC;