Advanced SQL: Metric Monitoring, Anomaly Detection, and Data Correctness at Scale Questions

Direct answer

Compute churn_date by looking for the FIRST point in each user's own chronological activity history where a gap of 30 or more consecutive days opens up between two events (using LAG() to compare each event against its predecessor), falling back to the OPEN-ENDED gap between the user's most recent activity and the analysis date if no such internal gap exists; separately, "currently active" is a different, simpler question, whether the user's most recent activity is within 30 days of the analysis date, and the two are not mutually exclusive: a user can have a churn_date from a past silence AND be currently active again if they later returned.

Structured elaboration

Two genuinely different questions, both worth answering. "When did this user first go 30 days silent" (churn_date, a historical fact that, once true, stays true even if the user later returns) and "is this user active right now" (a point-in-time status as of analysis_date) are not the same question, and the question's own second requirement, "show how to report users currently active," only makes sense as a SEPARATE check precisely because a user can have both a churn_date in their past AND be currently active again after reactivating.

Finding the first qualifying gap. Using LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) to pair each event with its immediately preceding event for the same user, then computing the day-gap between each consecutive pair; the FIRST (chronologically earliest) pair whose gap reaches 30+ days is what determines churn_date, set to 30 days after the gap's starting event (the moment the 30-day silence threshold was actually crossed).

The open-ended gap, a special case that needs its own check. A user whose LAST activity is followed by nothing (no later event at all) has no "next event" for LAG to compare against in the forward direction, so their potential churn from silence-since-last-activity has to be checked separately, comparing their MAX(occurred_at) against analysis_date itself, with churn_date = last_active + 30 days if that gap also reaches 30+.

Currently active, the second required output. days_since_last_active < 30 as of analysis_date, computed independently of whether a churn_date exists at all; this correctly lets a reactivated user (one who has a churn_date from an earlier silence but has since returned) show up as BOTH "has a churn_date" and "currently active," rather than forcing a single binary status that would lose that real nuance.

Worked example

Schema: user_events(user_id, occurred_at). Four users engineered to test the distinct scenarios: clean open-ended churn, a past-gap-then-reactivation, a user who never has a qualifying gap at all, and the exact-30-day boundary. Analysis date pinned to 2026-02-15:

CREATE TABLE user_events (user_id INTEGER, occurred_at TEXT);
INSERT INTO user_events VALUES
 (1,'2025-10-25'),(1,'2025-11-01'),                                                    -- u1: goes silent for good after Nov 1
 (2,'2025-11-01'),(2,'2026-02-10'),                                                     -- u2: one 101-day gap, then returns 5 days before analysis_date
 (3,'2025-10-01'),(3,'2025-10-25'),(3,'2025-11-18'),(3,'2025-12-12'),(3,'2026-01-05'),(3,'2026-01-29'), -- u3: every gap under 30 days
 (4,'2026-01-01'),(4,'2026-01-16');                                                     -- u4: last active exactly 30 days before analysis_date

WITH params AS (SELECT date('2026-02-15') AS analysis_date),
ordered AS (
    SELECT user_id, occurred_at,
        LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_occurred_at
    FROM user_events
),
gaps AS (
    SELECT user_id, prev_occurred_at, occurred_at,
        CAST(julianday(occurred_at) - julianday(prev_occurred_at) AS INTEGER) AS gap_days
    FROM ordered WHERE prev_occurred_at IS NOT NULL
),
first_qualifying_gap AS (
    SELECT user_id, MIN(prev_occurred_at) AS gap_start FROM gaps WHERE gap_days >= 30 GROUP BY user_id
),
last_activity_overall AS (SELECT user_id, MAX(occurred_at) AS last_active FROM user_events GROUP BY user_id),
open_ended_gap AS (
    SELECT la.user_id, la.last_active,
        CAST(julianday((SELECT analysis_date FROM params)) - julianday(la.last_active) AS INTEGER) AS days_since_last_active
    FROM last_activity_overall la
)
SELECT oe.user_id, oe.last_active, oe.days_since_last_active,
    CASE
        WHEN fqg.gap_start IS NOT NULL THEN date(fqg.gap_start, '+30 days')
        WHEN oe.days_since_last_active >= 30 THEN date(oe.last_active, '+30 days')
        ELSE NULL
    END AS churn_date,
    CASE WHEN oe.days_since_last_active < 30 THEN 1 ELSE 0 END AS currently_active
FROM open_ended_gap oe LEFT JOIN first_qualifying_gap fqg ON fqg.user_id = oe.user_id
ORDER BY oe.user_id;

Executed against SQLite. Output:

user_id | last_active | days_since_last_active | churn_date  | currently_active
1       | 2025-11-01  | 106                     | 2025-12-01  | 0
2       | 2026-02-10  | 5                       | 2025-12-01  | 1
3       | 2026-01-29  | 17                      | NULL        | 1
4       | 2026-01-16  | 30                      | 2026-02-15  | 0

u1: no internal 30+ day gap (only gap is 7 days), so churn_date comes from the open-ended check (Nov 1 + 30 = Dec 1), currently NOT active (106 days silent). u2: the internal gap between Nov 1 and Feb 10 is 101 days, which IS a qualifying internal gap, so churn_date = Dec 1 (30 days after Nov 1), but the user is currently active (returned Feb 10, only 5 days before analysis_date), demonstrating the exact "has a churn_date AND currently active" case the direct answer names. u3: every consecutive gap stays under 30 days, so no churn_date at all (NULL, correctly, not a fabricated date), currently active. u4: last active exactly 30 days before analysis_date, the boundary case, correctly treated as churned under a >=30 convention.

Complexity

Time: $O(n \log n)$ per user for the LAG window function's implicit sort by occurred_at (or $O(n)$ if the events are already stored sorted), where $n$ is that user's event count; overall $O(N \log N)$ across all users' events combined in the worst case. Space: $O(N)$ for the windowed intermediate results.

Edge cases

• A user with exactly ONE event ever: no prev_occurred_at exists, so no internal gap can be evaluated; their churn status depends entirely on the open-ended check against analysis_date.
• A user whose first-ever event is itself more than 30 days before analysis_date but who has since been consistently active: correctly NOT churned, since the relevant gap is only ever measured between CONSECUTIVE activity, never from account creation itself.

Trade-offs and pitfalls

• Computing churn_date only from the open-ended (last-activity-to-today) gap, and ignoring internal gaps entirely, would have missed u2's real historical churn event, understating how many users have EVER gone through a churn-and-reactivation cycle, a genuinely different population from users who have simply always been steadily active.
• Reporting "currently active" as though it contradicts having a churn_date is a common misreading of this two-part design; the two fields answer different questions and u2 in the worked example is the direct proof that both can legitimately be true at once.
• The >= 30 boundary convention (u4) is a choice, not a law of nature; a team could reasonably define churn as > 30 instead, and whichever convention is chosen should be stated explicitly, since it shifts exactly-boundary users like u4 in or out of the churned population.

Direct answer

Join orders to refunds to get a NET revenue figure per order, aggregate to net revenue per user, then apply the same per-user rolling z-score outlier test as a basic outlier check, being careful that the outlier candidate's own value doesn't inflate the baseline used to judge it. At 100M+ rows the main additional concern is making the join and the per-user aggregation cheap rather than the statistics itself.

Structured elaboration

• Net revenue: orders.amount - COALESCE(refunds.refund_amount, 0), joined on order_id. A LEFT JOIN is required since most orders have no matching refund row.
• Per-user outlier test: AVG/STDDEV_SAMP of net amount PARTITION BY user_id, then flag net_amount > user_mean + 3*user_std.
• Scale considerations for 100M+ rows: partition the refunds join and the per-user window on user_id so the engine can compute both efficiently without an expensive full shuffle -- a shuffle is the physical movement of rows between machines in a distributed engine so that every row sharing a key (here, the same user_id) ends up on the same machine before a per-user join or aggregate can be computed at all. If the engine supports it, a hash- or range-partitioned physical layout on user_id avoids a repartition step before the window function runs -- this is a different, physical-storage sense of "partition" from the PARTITION BY user_id window-function clause used above: that clause tells the engine how to group rows for computation, while a partitioned physical layout is about how the table's data already sits on disk/across nodes before the query starts. Precompute per-user aggregates (mean, count, sum of squares for standard deviation) as an incremental, maintained table rather than recomputing the full window over the whole history on every run, if this check needs to run frequently.

Worked example

One user's orders, one of which was partially refunded:

Computed with a window that includes ALL six rows in this user's own baseline (the naive version): user_mean = 169.2, user_std = 162.4, and order 6's z-score is only $(500-169.2)/162.4 \approx 2.04$, which does not cross a 3-sigma threshold, even though $500 net is clearly the odd one out among five orders around $90-120. This is the same self-masking failure any per-entity outlier check runs into when the baseline includes the row being judged, made worse here: the raw $900 charge (before the refund landed) would have pulled the baseline up even further had it been included un-refunded, and even the refund-adjusted $500 still inflates its own mean and standard deviation enough to blunt its own z-score. The fix is identical to the basic case: compute user_mean/user_std over the user's history EXCLUDING the row being tested, so the $500 net order is judged against a clean baseline of {100, 120, 90, 110, 95} (mean 103, std about 12), giving a z-score over 30, an unambiguous flag. As full, runnable queries:

-- naive: baseline includes the row being tested
WITH net AS (
  SELECT o.order_id, o.user_id, o.order_date,
         o.amount - COALESCE(r.refund_amount, 0) AS net_amount
  FROM orders o LEFT JOIN refunds r ON r.order_id = o.order_id
)
SELECT order_id, user_id, net_amount,
  AVG(net_amount) OVER w AS naive_mean,
  STDDEV_SAMP(net_amount) OVER w AS naive_std,
  (net_amount - AVG(net_amount) OVER w) / STDDEV_SAMP(net_amount) OVER w AS naive_z
FROM net
WINDOW w AS (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

-- fixed: baseline excludes the row being tested
WITH net AS (
  SELECT o.order_id, o.user_id, o.order_date,
         o.amount - COALESCE(r.refund_amount, 0) AS net_amount
  FROM orders o LEFT JOIN refunds r ON r.order_id = o.order_id
)
SELECT order_id, user_id, order_date, net_amount,
  baseline_mean, baseline_stddev,
  (baseline_stddev > 0 AND net_amount > baseline_mean + 3 * baseline_stddev) AS is_anomalous
FROM (
  SELECT order_id, user_id, order_date, net_amount,
    AVG(net_amount) OVER w AS baseline_mean,
    STDDEV_SAMP(net_amount) OVER w AS baseline_stddev
  FROM net
  WINDOW w AS (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
) t
ORDER BY order_id;

Running the naive version against this exact six-order history reproduces naive_mean = 169.17, naive_std = 162.43, naive_z ~= 2.04 at order 6 (the self-masked, non-flagging result described above); running the fixed version reproduces baseline_mean = 103.0, baseline_stddev ~= 12.04 at order 6, with is_anomalous = true.

Trade-offs & pitfalls

• Refund timing matters: if a refund can land days or weeks after the original order, decide whether net revenue is computed as-of the order date (requiring a later correction pass when a refund arrives) or as-of a report date (simpler but revises history). State this assumption; it changes how "outlier" is even defined for an order refunded after the check already ran.
• The self-masking bug is the single most likely defect in a first-draft answer to this question: reviewers should specifically check whether the baseline window excludes the row under test.
• At true 100M+ row scale, an unindexed refunds join or a non-partitioned window is the most common performance failure; partitioning/clustering both tables on user_id (or the equivalent physical layout for the engine in use) is the first lever, before reaching for approximate methods.

Direct answer

Split users into "before" and "after" cohorts by whether their created_at falls before or after the rollout date, then compute both 30-day retention and 30-day LTV relative to EACH user's own signup date (not a fixed calendar date), treating users with zero purchases as genuine $0 contributors to LTV via a LEFT JOIN rather than excluding them, since silently dropping non-purchasers would understate the true per-user economics of both periods.

Structured elaboration

Cohort split. period = CASE WHEN created_at < rollout_date THEN 'before' ELSE 'after' END, a single flag derived once and reused for both the retention and LTV computations, so the two metrics are computed over exactly the same two populations.

30-day retention, per user's own signup date. A user counts as retained if they have a SESSION on exactly created_at + 30 days (or, depending on the exact definition wanted, ANY session within the 30-day window; the query below uses the stricter exact-day version, and either definition should be stated explicitly).

30-day LTV, per user's own signup date. Sum of purchases.amount where purchased_at <= created_at + 30 days, divided by cohort size (not by purchaser count), so a user who never buys anything correctly contributes $0 and pulls the average down, exactly as intended for a genuine per-user LTV figure rather than an average-order-value-among-buyers number.

Handling users with no purchases. A LEFT JOIN from the cohort to purchases (not an INNER JOIN) is what makes a non-purchasing user still appear in the aggregation with a $0 contribution rather than disappearing from the calculation entirely; COALESCE(SUM(amount), 0) guards against a NULL sum for a user with zero matching purchase rows.

Worked example

Schema exactly as given, rollout date 2026-01-15. Four "before" users and four "after" users, engineered to test the exact-day-30 retention boundary and the 30-day LTV window boundary in both directions:

CREATE TABLE users (user_id INTEGER, created_at TIMESTAMP);
CREATE TABLE sessions (session_id INTEGER, user_id INTEGER, started_at TIMESTAMP, duration_seconds INTEGER);
CREATE TABLE purchases (purchase_id INTEGER, user_id INTEGER, amount NUMERIC, purchased_at TIMESTAMP);

INSERT INTO users VALUES (1,'2026-01-01'),(2,'2026-01-01'),(3,'2026-01-05'),(4,'2026-01-10'),
                          (5,'2026-01-16'),(6,'2026-01-16'),(7,'2026-01-20'),(8,'2026-01-25');
INSERT INTO sessions VALUES
 (1,1,'2026-01-31',300),   -- u1: session exactly on day 30 -> retained
 (2,2,'2026-01-02',120),   -- u2: no session near day 30 -> not retained
 (3,3,'2026-01-31',200),   -- u3: session on day 26 (not day 30) -> not retained under the exact-day definition
 (5,5,'2026-02-15',400),   -- u5: session exactly on day 30 -> retained
 (6,6,'2026-01-17',90),    -- u6: no session near day 30 -> not retained
 (7,7,'2026-02-19',250);   -- u7: session exactly on day 30 -> retained
INSERT INTO purchases VALUES
 (1,1,50.0,'2026-01-10'),(2,1,30.0,'2026-01-29'),   -- u1: $80 within 30 days
 (3,3,20.0,'2026-01-20'),                            -- u3: $20 within 30 days
 (4,5,60.0,'2026-02-01'),                            -- u5: $60 within 30 days
 (5,7,45.0,'2026-02-10'),                            -- u7: $45 within 30 days
 (6,8,90.0,'2026-03-15');                            -- u8: purchase on day 49, OUTSIDE the 30-day window

WITH params AS (SELECT date('2026-01-15') AS rollout_date),
cohort AS (
    SELECT user_id, created_at,
        CASE WHEN date(created_at) < (SELECT rollout_date FROM params) THEN 'before' ELSE 'after' END AS period
    FROM users
),
retained AS (
    SELECT c.user_id FROM cohort c
    WHERE EXISTS (SELECT 1 FROM sessions s WHERE s.user_id=c.user_id AND date(s.started_at)=date(c.created_at,'+30 days'))
),
ltv_30d AS (
    SELECT c.user_id, COALESCE(SUM(p.amount), 0) AS revenue_30d
    FROM cohort c LEFT JOIN purchases p ON p.user_id=c.user_id AND date(p.purchased_at) <= date(c.created_at,'+30 days')
    GROUP BY c.user_id
)
SELECT c.period, COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT r.user_id) AS retained_30d,
    ROUND(1.0*COUNT(DISTINCT r.user_id)/COUNT(DISTINCT c.user_id), 4) AS retention_rate_30d,
    ROUND(1.0*SUM(l.revenue_30d)/COUNT(DISTINCT c.user_id), 2) AS ltv_30d_per_user
FROM cohort c LEFT JOIN retained r ON r.user_id=c.user_id LEFT JOIN ltv_30d l ON l.user_id=c.user_id
GROUP BY c.period;

Executed against SQLite. Output:

period | cohort_size | retained_30d | retention_rate_30d | ltv_30d_per_user
after  | 4           | 2            | 0.5                 | 26.25
before | 4           | 1            | 0.25                | 25.0

Before: only u1 is retained (session exactly on day 30); u3's session lands on day 26, NOT day 30, correctly excluded under the strict exact-day definition, illustrating why the definition needs to be stated precisely, a "day 26" session reads as "the user came back" informally but does not satisfy "retained AT day 30" specifically. Retention: 1/4 = 0.25. LTV: u1 contributes $80 (both purchases fall within 30 days of Jan 1), u3 contributes $20, u2 and u4 contribute $0; total $100/4 = $25.00. After: u5 and u7 both retained (sessions exactly on their respective day 30), 2/4 = 0.5. LTV: u5 contributes $60, u7 contributes $45, u8's purchase on day 49 falls OUTSIDE the 30-day window and correctly contributes $0 despite being a real purchase, u6 contributes $0; total $105/4 = $26.25.

Trade-offs and pitfalls

• The exact-day-30 retention definition used here is a real, consequential choice, not the only valid one; u3's day-26 session shows exactly how a "retained BY day 30" (any activity through day 30) definition would produce a different, HIGHER before-cohort retention rate (2/4 = 0.5 instead of 1/4 = 0.25) for the identical underlying data, so the chosen definition should always be stated alongside the resulting number.
• Comparing before/after cohorts around a single rollout date conflates the rollout's effect with ordinary calendar-time drift (seasonality, a concurrent marketing change, or simple cohort-to-cohort noise from small sample sizes); a rigorous read of "did the rollout work" needs either a genuine randomized holdout run alongside the rollout, or, at minimum, several cohorts on each side of the boundary rather than just one immediately-adjacent pair, to distinguish a real rollout effect from noise.
• An INNER JOIN to purchases instead of the LEFT JOIN shown would silently drop every non-purchasing user from the LTV calculation, inflating the reported LTV by computing it only over buyers; the deliberate LEFT JOIN plus COALESCE is what keeps the denominator honest at the full cohort size.

Direct answer

DAU, WAU, and MAU are all the SAME underlying computation (distinct active users in a trailing window ending on a reference date), differing only in window length (1, 7, and 30 days respectively); computing all three efficiently for a large sliding history means avoiding a naive per-day full re-scan and instead either pre-aggregating daily distinct-user sets or using an incrementally-maintained approximate structure at very large scale.

Structured elaboration

The shared structure. Each of DAU, WAU, MAU is COUNT(DISTINCT user_id) WHERE event_date BETWEEN (ref_date - window + 1) AND ref_date, with window = 1, 7, 30. Writing all three as parallel subqueries (or CTEs) against the same base events table, sharing a single reference-date parameter, keeps the three numbers internally consistent (computed from the exact same underlying activity definition and reference point) rather than risking drift if they were computed by three separately-written queries at different times.

DAU/WAU as the "within-week" stickiness signal, distinct from DAU/MAU. DAU/WAU answers "of the people active this WEEK, how many show up on a typical single day," a tighter, faster-moving signal than DAU/MAU, useful for products with a strong weekly cadence where the interesting question is short-term consistency rather than month-scale reach.

Efficiency for distinct users over sliding windows, the real engineering question. A naive approach recomputes COUNT(DISTINCT user_id) from scratch for every reference date by re-scanning the full window's raw events, which is wasteful when adjacent reference dates share almost all of their underlying data (today's MAU window and yesterday's MAU window overlap in 29 of their 30 days). Two standard efficiency improvements: (1) PRE-AGGREGATE the base fact to one row per (user_id, activity_date) (deduplicated) once, so that all subsequent sliding-window queries at least start from a much smaller, deduplicated base rather than raw event volume; (2) for the sliding computation itself, either maintain a per-day distinct-user COUNT that can be summed incrementally is NOT valid for a distinct-count sliding window (since a user active on both an entering day and an already-counted day would be double-subtracted on removal), so exact distinct-count sliding windows generally still require a windowed re-aggregation over the deduplicated (user_id, date) base, whereas at very large scale (billions of users), an APPROXIMATE distinct-count structure (a probabilistic sketch that supports incremental merge/unmerge as the window slides) trades a small, bounded error for the ability to update the count incrementally rather than recomputing the full distinct set on every slide.

Worked example

Schema: events(user_id, event_date). Six rows across 4 users, with a duplicate row for one user to test deduplication, reference date 2026-01-31:

CREATE TABLE events (user_id INTEGER, event_date TEXT);
INSERT INTO events VALUES
 (1,'2026-01-31'),                                    -- active today: counts in DAU, WAU, MAU
 (2,'2026-01-28'),                                     -- active this week, not today: WAU, MAU only
 (3,'2026-01-10'),                                     -- active this month, not this week: MAU only
 (4,'2025-12-01'),                                     -- active over 30 days ago: none of the three
 (5,'2026-01-31'), (5,'2026-01-31'), (5,'2026-01-30'); -- duplicate row for u5, must not double count

WITH ref AS (SELECT date('2026-01-31') AS ref_date),
activity AS (SELECT DISTINCT user_id, date(event_date) AS d FROM events)
SELECT
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d = ref_date) AS dau,
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d BETWEEN date(ref_date,'-6 days') AND ref_date) AS wau,
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d BETWEEN date(ref_date,'-29 days') AND ref_date) AS mau;

Executed against SQLite. Output:

dau | wau | mau
2   | 3   | 4

DAU/WAU $= 2/3 \approx 0.6667$, DAU/MAU $= 2/4 = 0.5$. Manual check: DAU (active exactly on 01-31) is users 1 and 5, since 5's duplicate row still counts as one user; WAU (01-25 through 01-31) adds user 2 (active 01-28), giving {1,2,5}=3; MAU (01-02 through 01-31) adds user 3 (active 01-10), giving {1,2,3,5}=4; user 4 (active 2025-12-01, more than 30 days before the reference date) never enters any of the three windows.

Complexity

Time for a single reference date: $O(n)$ over the events considered within the largest window (MAU's 30 days), since the query is effectively three overlapping distinct-count aggregations sharing the same deduplicated base. Space: $O(u)$ for the distinct users in the widest window. Computing this for EVERY day over a long history naively (re-running the full query once per day) costs $O(d \times n)$ for $d$ days of history, which is where the pre-aggregation/incremental-structure discussion above becomes a genuine, not just theoretical, efficiency concern at scale.

Edge cases

• A user active only on the FIRST day of the 30-day MAU window (day 30 back from reference): correctly included in MAU, correctly excluded from WAU and DAU, since they fall outside those narrower windows.
• All activity for a user landing on exactly the reference date, with multiple duplicate rows (as with user 5): collapses correctly to one distinct-user contribution in every window that includes that date, confirming the DISTINCT step is load-bearing, not decorative.

Trade-offs and pitfalls

• A common performance mistake is computing this per reference date by re-scanning raw (non-deduplicated) events every time, rather than building the deduplicated (user_id, date) base once; at real event volumes (many events per user per day), this can mean re-scanning an order of magnitude more rows than necessary for every single day's DAU/WAU/MAU computation.
• Naively trying to maintain an EXACT distinct count incrementally by adding the entering day and subtracting the exiting day is mathematically wrong, since a user active on BOTH the exiting day and a still-in-window day would be incorrectly removed from the running total; this is exactly why exact sliding distinct-counts either re-aggregate over the window or require a more careful structure (a per-user last-seen-date index) rather than a naive running counter.
• Reaching for an approximate (probabilistic) distinct-count structure before the exact approach has actually become a measured bottleneck is premature optimization; the deduplicated-base-plus-windowed-aggregation approach shown here comfortably handles a wide range of real-world scales, and the approximate-structure discussion is included here as the honest answer to "how would this scale further," not as a default recommendation for every retention pipeline.

Direct answer

Model retention and revenue at the PLAN level, not the customer level: a customer who downgrades or pauses is still "retained" as a customer even though their plan-level revenue changed, so the cohort curve needs separate logic for whether the CUSTOMER stayed versus whether the ORIGINAL PLAN'S revenue was retained, expanded, or contracted, with proration and refunds explicitly reconciled rather than assumed away.

Structured elaboration

• Why customer-level and plan-level definitions diverge: a customer who downgrades from a $100/mo plan to a $60/mo plan has NOT churned as a customer (they're still paying, still active), but has produced $40/mo of REVENUE churn on their original cohort's plan-level curve. Conflating the two produces a retention curve that's technically not wrong for either definition alone, but answers neither question cleanly if the two are mixed.
• Renewals, expansions, downgrades, and churn each need their own bucket: renewal (same price continues), expansion (price increases, e.g. an upsell or add-on), downgrade (price decreases but customer remains), and full churn (customer stops paying entirely). A revenue-retention curve typically reports NET revenue retention, which nets expansions against downgrades and churn within the same cohort.
• Proration: a plan change partway through a billing period produces a partial-period charge at each of the old and new price; decide whether the cohort curve attributes that month's revenue at the blended prorated amount or simplifies to whichever plan was active longer that month, and state the choice.
• Multiple overlapping subscriptions per customer (a customer with two products, or a trial running alongside an existing paid plan) require deciding whether the cohort curve is computed per subscription or aggregated per customer; these produce different, both legitimate, answers.
• Validating the accuracy of the resulting curves: reconcile the SUM of a cohort's monthly plan-level revenue at each offset against an independent GAAP (Generally Accepted Accounting Principles)-style recognized-revenue figure -- i.e., the revenue number finance already reports for accounting purposes -- for the same customers and period, since a validated cohort curve should tie out to the finance team's own numbers, not just internally to itself.

Worked example

A cohort of 100 customers who signed up in the same month, each starting on a $50/mo plan. Suppose by month 3: 70 are still on the original $50/mo plan (pure renewal), 10 upgraded to $80/mo (expansion, +$30/mo each), 10 downgraded to $30/mo (downgrade, -$20/mo each), and 10 churned entirely (-$50/mo each). The cohort's month-3 plan-level revenue is $70(50) + 10(80) + 10(30) + 10(0) = 3500+800+300+0 = 4600$, against an original month-0 revenue of $100 \times 50 = 5000$. Net revenue retention at month 3 is $4600/5000 = 92\%$: expansions ($300 extra from upgrades) partially offset downgrades and churn ($700 lost combined), landing at 92% even though 10 of the original 100 customers stopped paying entirely. A customer-level retention curve for the same cohort at month 3 would instead report 90 customers still active (excluding only the 10 who fully churned), a DIFFERENT number answering a different question; presenting only one of these two curves without naming which one it is invites a stakeholder to draw the wrong conclusion from it.

A representative query producing the plan-level cohort curve directly from the schema in the question:

WITH cohort AS (
  SELECT customer_id, DATE_TRUNC('month', start_date) AS cohort_month
  FROM subscriptions
),
monthly_revenue AS (
  SELECT customer_id, DATE_TRUNC('month', event_date) AS revenue_month, SUM(amount) AS revenue
  FROM events
  WHERE event_type = 'payment'
  GROUP BY customer_id, DATE_TRUNC('month', event_date)
)
SELECT c.cohort_month,
  DATEDIFF('month', c.cohort_month, mr.revenue_month) AS month_offset,
  SUM(mr.revenue) AS cohort_revenue,
  COUNT(DISTINCT mr.customer_id) AS paying_customers
FROM cohort c
JOIN monthly_revenue mr ON mr.customer_id = c.customer_id
GROUP BY c.cohort_month, month_offset
ORDER BY c.cohort_month, month_offset;

Run against the 100-customer cohort above, this returns month_offset = 0 with cohort_revenue = 5000 and paying_customers = 100, and month_offset = 3 with cohort_revenue = 4600 and paying_customers = 90, reproducing both the 92% net revenue retention and the 90% customer-level retention computed above directly from the query rather than by hand.

Trade-offs & pitfalls

• A single "retention rate" without specifying customer-level vs. plan-level revenue is ambiguous and easy to misinterpret: always label which definition a chart is using, since the two can diverge substantially (as in the worked example, 90% customer retention vs. 92% net revenue retention are both true simultaneously and mean different things).
• Proration decisions materially change the numbers in a plan-change-heavy business and should be documented as an explicit modeling choice, not left as an implicit side effect of however the query happened to be written.
• Multiple subscriptions per customer is a common real-world wrinkle that a simplified one-subscription-per-customer model will get wrong; decide and document the unit of analysis (subscription vs. customer) up front.