How to Implement Growth Accounting Using SQL Window Functions: A Complete Data Engineering Guide

Use a FULL OUTER JOIN between yesterday's state table and today's events, classify users with CASE logic, then apply window functions like SUM() OVER and LAG() OVER to compute cumulative growth, rolling retention, and period-over-period deltas.

Growth accounting using SQL window functions is a foundational analytical pattern for tracking how users move through lifecycle states—new, retained, resurrected, churned, and stale—without relying on external orchestration or temporary tables. The DataExpert-io/data-engineer-handbook repository provides production-ready implementations of this pattern, demonstrating how modern data warehouses can compute sophisticated growth metrics in pure SQL.

Core Architecture of Growth Accounting

The growth accounting system in intermediate-bootcamp/materials/4-applying-analytical-patterns/ follows a multi-layer design that separates event ingestion, state snapshot generation, and metric computation.

The users_growth_accounting Fact Table

At the center of the architecture lies the users_growth_accounting table, defined in intermediate-bootcamp/materials/4-applying-analytical-patterns/tables/user_growth_accounting.sql. This table stores one row per user per day with the following critical fields:

• user_id – unique identifier
• date – the snapshot date
• first_active_date – when the user first appeared
• last_active_date – most recent activity prior to or on this date
• daily_active_state – classification: 'New', 'Retained', 'Resurrected', 'Churned', or 'Stale'
• weekly_active_state – weekly aggregation of the same states
• dates_active – array of all active dates for the user

This schema enables both point-in-time analysis and historical reconstruction of user journeys.

Daily Snapshot Generation

The growth_accounting.sql file implements the core transformation logic. Each run performs three operations:

1. Aggregate today's events – group raw events by user_id and date
2. Retrieve yesterday's state – select from users_growth_accounting for the previous day
3. FULL OUTER JOIN – combine both datasets so users appear regardless of which day they were active

The FULL OUTER JOIN is essential because it handles four scenarios simultaneously: users active only today (new), only yesterday (potential churn), both days (retained), or neither (stale records).

State Classification Logic

After joining, a CASE expression in growth_accounting.sql assigns the daily state:

CASE
    WHEN y.user_id IS NULL 
        THEN 'New'
    WHEN y.last_active_date = t.event_date - INTERVAL '1 day' 
        THEN 'Retained'
    WHEN y.last_active_date < t.event_date - INTERVAL '1 day' 
        THEN 'Resurrected'
    WHEN t.event_date IS NULL AND y.last_active_date = y.date 
        THEN 'Churned'
    ELSE 'Stale'
END AS daily_active_state

This logic depends entirely on comparing last_active_date from the prior snapshot against the current date, making it stateful and efficient.

Computing Growth Metrics with Window Functions

Once daily states are persisted, SQL window functions enable complex analytics without self-joins or subqueries. The window_based_analysis.sql file demonstrates the fundamental patterns: cumulative sums, rolling windows, and lag comparisons.

Cumulative New User Counts

To track total user acquisition over time:

SUM(CASE WHEN daily_active_state = 'New' THEN 1 ELSE 0 END) 
    OVER (ORDER BY date) AS cum_new_users

This running total answers "how many unique users have we ever acquired by each date?"

Rolling Active User Windows

For weekly active user (WAU) calculations:

SUM(CASE WHEN daily_active_state IN ('New', 'Retained', 'Resurrected') 
         THEN 1 ELSE 0 END) 
    OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
    AS weekly_active_users

The ROWS BETWEEN frame specification creates a sliding 7-day window that updates with each day.

Period-over-Period Growth Deltas

To compute week-over-week growth without explicit joins:

weekly_active_users - 
    LAG(weekly_active_users, 7) OVER (ORDER BY date) 
    AS wow_growth_delta

Alternatively, compare consecutive days for daily growth velocity:

daily_active_users - 
    LAG(daily_active_users) OVER (ORDER BY date) 
    AS wow_growth_delta

The LAG() function accesses prior rows in the ordered partition, eliminating the need for correlated subqueries.

Complete Implementation Example

The following production-ready SQL implements the full pipeline from event ingestion to growth metrics, based on patterns from growth_accounting.sql and window_based_analysis.sql:

Step 1: Build and Persist Daily Snapshots

-- Aggregate today's events
WITH today_events AS (
    SELECT 
        user_id,
        DATE(event_time) AS event_date,
        COUNT(*) AS event_count
    FROM events
    WHERE DATE(event_time) = CURRENT_DATE
    GROUP BY user_id, DATE(event_time)
),

-- Retrieve yesterday's growth accounting state
yesterday_state AS (
    SELECT *
    FROM users_growth_accounting
    WHERE date = CURRENT_DATE - INTERVAL '1 day'
),

-- Generate today's snapshot with state classification
snapshot AS (
    SELECT
        COALESCE(t.user_id, y.user_id) AS user_id,
        COALESCE(y.first_active_date, t.event_date) AS first_active_date,
        COALESCE(t.event_date, y.last_active_date) AS last_active_date,
        CASE
            WHEN y.user_id IS NULL THEN 'New'
            WHEN y.last_active_date = t.event_date - INTERVAL '1 day' 
                THEN 'Retained'
            WHEN y.last_active_date < t.event_date - INTERVAL '1 day' 
                THEN 'Resurrected'
            WHEN t.user_id IS NULL AND y.last_active_date = y.date 
                THEN 'Churned'
            ELSE 'Stale'
        END AS daily_active_state,
        COALESCE(y.dates_active, ARRAY[]::DATE[]) ||
            CASE WHEN t.user_id IS NOT NULL 
                 THEN ARRAY[t.event_date] 
                 ELSE ARRAY[]::DATE[] 
            END AS dates_active,
        COALESCE(t.event_date, y.date + INTERVAL '1 day') AS date
    FROM today_events t
    FULL OUTER JOIN yesterday_state y USING (user_id)
)

-- Upsert into the growth accounting table
INSERT INTO users_growth_accounting
SELECT * FROM snapshot
ON CONFLICT (user_id, date) DO UPDATE SET
    first_active_date = EXCLUDED.first_active_date,
    last_active_date = EXCLUDED.last_active_date,
    daily_active_state = EXCLUDED.daily_active_state,
    dates_active = EXCLUDED.dates_active;

Step 2: Compute Growth Accounting Metrics

WITH daily_states AS (
    SELECT 
        date,
        daily_active_state,
        COUNT(DISTINCT user_id) AS user_count
    FROM users_growth_accounting
    GROUP BY date, daily_active_state
),

-- Pivot states to columns
pivoted AS (
    SELECT
        date,
        SUM(CASE WHEN daily_active_state = 'New' THEN user_count END) 
            AS new_users,
        SUM(CASE WHEN daily_active_state = 'Retained' THEN user_count END) 
            AS retained_users,
        SUM(CASE WHEN daily_active_state = 'Resurrected' THEN user_count END) 
            AS resurrected_users,
        SUM(CASE WHEN daily_active_state = 'Churned' THEN user_count END) 
            AS churned_users,
        SUM(CASE WHEN daily_active_state = 'Stale' THEN user_count END) 
            AS stale_users
    FROM daily_states
    GROUP BY date
),

-- Apply window functions for growth metrics
metrics AS (
    SELECT
        date,
        new_users,
        retained_users,
        resurrected_users,
        churned_users,
        stale_users,
        
        -- Cumulative metrics
        SUM(new_users) OVER (ORDER BY date) 
            AS cumulative_new_users,
        
        -- Rolling 7-day active (New + Retained + Resurrected)
        SUM(new_users + retained_users + resurrected_users) 
            OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
            AS weekly_active_users,
        
        -- Week-over-week growth using LAG
        SUM(new_users + retained_users + resurrected_users) 
            OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) -
        LAG(SUM(new_users + retained_users + resurrected_users) 
            OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 7) 
            OVER (ORDER BY date) 
            AS wow_growth_delta,
        
        -- Quick ratio: (New + Resurrected) / Churned
        (new_users + resurrected_users)::FLOAT / 
            NULLIF(churned_users, 0) AS quick_ratio
    FROM pivoted
)

SELECT *
FROM metrics
ORDER BY date;

Retention Analysis with Window Functions

The retention_analysis.sql file extends this foundation to compute cohort retention curves. By partitioning window functions on first_active_date (the cohort), you can calculate classic retention metrics:

SELECT
    first_active_date AS cohort_date,
    date - first_active_date AS days_since_first_active,
    COUNT(DISTINCT CASE WHEN daily_active_state IN ('Retained', 'Resurrected') 
                        THEN user_id END) AS active_users,
    COUNT(DISTINCT CASE WHEN daily_active_state = 'New' 
                        THEN user_id END) AS cohort_size,
    
    -- Retention rate using window functions
    COUNT(DISTINCT CASE WHEN daily_active_state IN ('Retained', 'Resurrected') 
                        THEN user_id END)::FLOAT /
        FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
            PARTITION BY first_active_date 
            ORDER BY date
        ) AS retention_rate
FROM users_growth_accounting
WHERE daily_active_state != 'Stale'
GROUP BY first_active_date, date - first_active_date;

The FIRST_VALUE window function captures the cohort's initial size for consistent denominator calculation across all periods.

Performance Optimization Strategies

When implementing growth accounting using SQL window functions at scale, consider these optimizations from the Data Engineer Handbook patterns:

• Partition pruning – filter date columns aggressively in subqueries before window function evaluation
• Incremental processing – process only new dates rather than full history; the stateful design in growth_accounting.sql supports this naturally
• Appropriate distribution keys – distribute users_growth_accounting on user_id for joins, sort on date for window function efficiency
• Materialized pre-aggregates – the retention_analysis.sql pattern can be scheduled as a materialized view for dashboard consumption

Summary

• Growth accounting using SQL window functions tracks user state transitions through a FULL OUTER JOIN between yesterday's state and today's events, eliminating complex orchestration.
• The users_growth_accounting table in DataExpert-io/data-engineer-handbook provides a proven schema with daily and weekly state columns plus active date arrays.
• State classification uses CASE logic comparing last_active_date to identify New, Retained, Resurrected, Churned, and Stale users.
• Window functions (SUM() OVER, LAG() OVER, FIRST_VALUE() OVER) compute cumulative growth, rolling active users, and retention metrics without self-joins.
• The repository's window_based_analysis.sql demonstrates the foundational patterns applied in growth_accounting.sql and retention_analysis.sql.

Frequently Asked Questions

What database systems support these window function patterns for growth accounting?

All modern cloud data warehouses implement the SQL:2003 window function specification used in these examples. Snowflake, BigQuery, Redshift, Databricks, and DuckDB all support OVER, ROWS BETWEEN, LAG(), and FIRST_VALUE(). Minor syntax differences exist—BigQuery uses DATE() instead of DATE(), and interval literals vary—but the core patterns from growth_accounting.sql translate directly.

How does the FULL OUTER JOIN handle users who skip days?

Users with gaps in activity are classified as Resurrected when they return, based on the condition y.last_active_date < t.event_date - INTERVAL '1 day'. The FULL OUTER JOIN ensures these users appear in the output even when today_events has no matching row, allowing the CASE logic to evaluate their stale state and subsequent resurrection correctly.

Can this pattern scale to billions of events per day?

Yes. The stateful design where each run only processes one day of events and joins against a compact state table (one row per user per day) makes the computational complexity O(daily active users) rather than O(historical events). For extreme scale, partition users_growth_accounting by date and distribute by user_id hash to parallelize the FULL OUTER JOIN across nodes.

What's the difference between daily and weekly active states in the table?

The daily_active_state column applies the classification logic based on day-over-day comparisons, while weekly_active_state in users_growth_accounting.sql uses a 7-day lookback to smooth short-term fluctuations. Weekly states are useful for reducing noise in retention analysis and aligning reporting with business calendar periods, as demonstrated in retention_analysis.sql.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}