SELECT

DATE_TRUNC('day', dp.deployment_block_timestamp) as deployment_date,

dp.edition,

dp.program_id

FROM ALEO.core.dim_programs dp

WHERE deployment_block_timestamp >= CURRENT_DATE - INTERVAL '90 days'

),

daily_deployers AS (

SELECT

DATE_TRUNC('day', ft.block_timestamp) as date,

REGEXP_SUBSTR(ft.fee_msg['transition']['outputs'][0]['value'], 'aleo[\\w]+') AS developer_address,

pd.edition

FROM aleo.core.fact_transactions ft

JOIN program_deployments pd

ON DATE_TRUNC('day', ft.block_timestamp) = pd.deployment_date

WHERE ft.tx_succeeded = TRUE

AND developer_address IS NOT NULL

),

first_appearances AS (

SELECT

developer_address,

MIN(date) as first_seen_date,

edition

FROM daily_deployers

GROUP BY developer_address, edition

),

developer_metrics AS (

SELECT

d.date,

d.edition,

COUNT(DISTINCT d.developer_address) as daily_active_developers,

COUNT(DISTINCT CASE

WHEN d.date = f.first_seen_date THEN d.developer_address

END) as new_developers

FROM daily_deployers d