# How to Implement Growth Accounting with SQL: A Production-Ready Pattern

> Learn to implement growth accounting with SQL using a production-ready pattern. Build a persistent fact table and an incremental pipeline to classify users effectively.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-09

---

**Implement growth accounting with SQL by creating a persistent fact table that stores per-user daily states, then run an incremental pipeline that full outer joins yesterday's snapshot with today's events to classify users as New, Retained, Resurrected, Churned, or Stale using date arithmetic and conditional logic.**

Growth accounting is a framework for classifying user engagement into distinct lifecycle states on a daily and weekly basis. In the **Data Engineer Handbook** repository (`DataExpert-io/data-engineer-handbook`), this pattern is implemented using pure SQL to track transitions between activity states. This guide walks through the exact implementation found in [`intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/growth_accounting.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/growth_accounting.sql) to help you build a deterministic, idempotent pipeline suitable for production scheduling.

## Understanding Growth Accounting States

Growth accounting categorizes every user into exactly one state based on their activity patterns relative to previous days. The **Data Engineer Handbook** implementation recognizes five distinct states:

- **New**: First-time active users who have no prior history in the system.
- **Retained**: Users who were active yesterday (or within the last 7 days for weekly calculations) and are active again today.
- **Resurrected**: Previously active users who experienced a gap in activity (more than 1 day or 7 days) but returned today.
- **Churned**: Users who were active yesterday but show no activity today.
- **Stale**: Users who churned previously and remain inactive.

The SQL implementation computes these states by comparing the **last active date** against the current date using `INTERVAL` arithmetic.

## Setting Up the Growth Accounting Fact Table

Before running the incremental logic, you need a persistent table to store historical states. According to [`intermediate-bootcamp/materials/4-applying-analytical-patterns/tables/user_growth_accounting.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/4-applying-analytical-patterns/tables/user_growth_accounting.sql), the schema includes an array column to track all active dates for cohort analysis.

```sql
-- Path: intermediate-bootcamp/materials/4-applying-analytical-patterns/tables/user_growth_accounting.sql
CREATE TABLE users_growth_accounting (
    user_id TEXT,
    first_active_date DATE,
    last_active_date DATE,
    daily_active_state TEXT,
    weekly_active_state TEXT,
    dates_active DATE[],
    date DATE,
    PRIMARY KEY (user_id, date)
);

```

The `dates_active` array maintains a rolling history of every date the user was active, enabling downstream lifetime value (LTV) and retention analyses without scanning raw event tables.

## The Incremental SQL Algorithm

The core growth accounting logic follows a six-step incremental pattern designed to run daily. This approach minimizes compute costs by only processing new events and yesterday's state rather than rebuilding the entire history.

### Step 1: Capture Yesterday's State

Query the fact table for the previous day's accounting records to establish the baseline user states.

```sql
WITH yesterday AS (
    SELECT * 
    FROM users_growth_accounting 
    WHERE date = DATE('2023-03-09')
),

```

### Step 2: Aggregate Today's Events

Compute today's activity by grouping raw events by user and truncating timestamps to the day boundary.

```sql
today AS (
    SELECT
        CAST(user_id AS TEXT) AS user_id,
        DATE_TRUNC('day', event_time::timestamp) AS today_date,
        COUNT(1) AS cnt
    FROM events
    WHERE DATE_TRUNC('day', event_time::timestamp) = DATE('2023-03-10')
      AND user_id IS NOT NULL
    GROUP BY user_id, DATE_TRUNC('day', event_time::timestamp)
)

```

### Step 3: Merge with Full Outer Join

Use a **full outer join** to combine yesterday's states with today's events. This ensures every user appears exactly once, covering four critical scenarios: active both days, active only yesterday (churn), active only today (new), and inactive both days (stale).

```sql
FROM today t
FULL OUTER JOIN yesterday y ON t.user_id = y.user_id

```

### Step 4: Classify Activity States

Apply `CASE` expressions using date arithmetic to determine the daily and weekly states. The logic checks the gap between `last_active_date` and `today_date` using `INTERVAL` comparisons.

```sql
SELECT
    COALESCE(t.user_id, y.user_id) AS user_id,
    COALESCE(y.first_active_date, t.today_date) AS first_active_date,
    COALESCE(t.today_date, y.last_active_date) AS last_active_date,
    
    /* Daily state classification */
    CASE
        WHEN y.user_id IS NULL THEN 'New'
        WHEN y.last_active_date = t.today_date - INTERVAL '1 day' THEN 'Retained'
        WHEN y.last_active_date < t.today_date - INTERVAL '1 day' THEN 'Resurrected'
        WHEN t.today_date IS NULL AND y.last_active_date = y.date THEN 'Churned'
        ELSE 'Stale'
    END AS daily_active_state,
    
    /* Weekly state classification */
    CASE
        WHEN y.user_id IS NULL THEN 'New'
        WHEN y.last_active_date < t.today_date - INTERVAL '7 day' THEN 'Resurrected'
        WHEN t.today_date IS NULL 
             AND y.last_active_date = y.date - INTERVAL '7 day' THEN 'Churned'
        WHEN COALESCE(t.today_date, y.last_active_date) + INTERVAL '7 day' >= y.date 
            THEN 'Retained'
        ELSE 'Stale'
    END AS weekly_active_state,

```

### Step 5: Maintain Historical Arrays

Append today's date to the existing `dates_active` array using the concatenation operator `||`. This preserves the complete activity history for each user.

```sql
    /* Rolling date list maintenance */
    COALESCE(y.dates_active, ARRAY[]::DATE[]) ||
        CASE 
            WHEN t.user_id IS NOT NULL THEN ARRAY[t.today_date] 
            ELSE ARRAY[]::DATE[] 
        END AS date_list,
    
    COALESCE(t.today_date, y.date + INTERVAL '1 day') AS date
FROM today t
FULL OUTER JOIN yesterday y ON t.user_id = y.user_id

```

## Production Deployment and Idempotency

For production pipelines, wrap the query in an upsert operation to handle reruns gracefully. This pattern uses `ON CONFLICT` to overwrite existing records if the job restarts, ensuring the `users_growth_accounting` table remains consistent.

```sql
INSERT INTO users_growth_accounting
    (user_id, first_active_date, last_active_date,
     daily_active_state, weekly_active_state,
     dates_active, date)
SELECT
    user_id, first_active_date, last_active_date,
    daily_active_state, weekly_active_state,
    date_list, date
FROM (<the CTE query above>) AS src
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,
    weekly_active_state = EXCLUDED.weekly_active_state,
    dates_active = EXCLUDED.dates_active;

```

Schedule this SQL to run daily via **Apache Airflow**, **dbt**, or a managed notebook to maintain a continuously updated view of user growth metrics.

## Summary

Implementing growth accounting with SQL requires a stateful incremental pattern that tracks user transitions efficiently:

- Create a **fact table** (`users_growth_accounting`) with composite primary key on `(user_id, date)` and an array column for historical tracking.
- Use a **full outer join** between yesterday's snapshot and today's events to capture all state transitions including churn and resurrection.
- Apply **date interval arithmetic** to classify users into New, Retained, Resurrected, Churned, or Stale states.
- Maintain **idempotency** through upsert operations to support reliable daily scheduling.
- Store the complete logic in version-controlled files like [`intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/growth_accounting.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/growth_accounting.sql) for reproducibility.

## Frequently Asked Questions

### What is the difference between daily and weekly growth accounting states?

Daily states measure activity with a 1-day lookback window, classifying users as Retained only if they were active yesterday. Weekly states use a 7-day window, considering users Retained if they were active within the last week. The SQL implementation in [`growth_accounting.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/growth_accounting.sql) computes both simultaneously using `INTERVAL '1 day'` and `INTERVAL '7 day'` comparisons against the `last_active_date`.

### Why use a full outer join instead of a left join for growth accounting?

A **full outer join** captures four distinct transition scenarios that a left join would miss: users active yesterday but not today (Churned), users active today but not yesterday (New or Resurrected), users active both days (Retained), and users inactive both days (Stale). Left joins only preserve records from one side, forcing you to run separate queries for churned users, which complicates the pipeline and risks inconsistent state calculations.

### How do you handle late-arriving data in a growth accounting SQL pipeline?

Late-arriving events require reprocessing the affected dates. Because the `users_growth_accounting` table uses date partitioning, you can delete rows for the impacted dates and rerun the incremental job for those specific days. The idempotent `INSERT ... ON CONFLICT` pattern ensures that rerunning the job for historical dates correctly updates the `last_active_date`, `dates_active` array, and state classifications without creating duplicates.

### What schema optimizations improve query performance for growth accounting tables?

Index the composite primary key `(user_id, date)` to optimize the `WHERE date =` filter used when loading yesterday's state. Partition the table by the `date` column to enable efficient pruning of historical partitions during daily incremental loads. For the `dates_active` array column, consider using PostgreSQL's GIN indexes if you frequently query for specific activity patterns, though standard B-tree indexes on `user_id` and `date` typically suffice for the daily incremental pattern.