Meta Business Intelligence Analyst (Junior Level) - Complete Interview Preparation Guide

Compute each user's own historical mean and standard deviation using window functions restricted to PRIOR rows only, so a transaction is judged against what was normal for that user before it happened, not including itself in its own baseline.

Approach (verified by execution)

```sql
WITH stats AS (
SELECT transaction_id, user_id, amount, occurred_at,
AVG(amount) OVER (PARTITION BY user_id ORDER BY occurred_at
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS running_mean,
STDDEV_SAMP(amount) OVER (PARTITION BY user_id ORDER BY occurred_at
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS running_std,
COUNT(*) OVER (PARTITION BY user_id ORDER BY occurred_at
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prior_count
FROM transactions
)
SELECT transaction_id, user_id, amount,
(prior_count >= 5 AND running_std > 0 AND amount > running_mean + 3 * running_std) AS is_anomalous
FROM stats;
```
The frame `ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING` excludes the current row from its own baseline, which matters: including the current (possibly huge) transaction in the mean and standard deviation used to judge it would pull the threshold up and could make even a genuine anomaly look "within range."

Worked example (verified by execution)

For a user with five prior transactions around 9-13 and a sixth transaction of 500: the running mean and standard deviation at that sixth row (computed only from the first five) are roughly 11 and 1.58, so the threshold is about 15.7, and 500 correctly flags as anomalous. A second user with only two prior transactions is correctly excluded from flagging entirely, since `prior_count >= 5` isn't met.

Trade-offs and pitfalls

Excluding users with too little history avoids a specific false-positive trap: a second transaction that happens to differ even slightly from a single prior one can produce a technically enormous z-score, since the standard deviation of two points is a poor estimate. The threshold of "5 prior transactions" is a reasonable, defensible floor, not a universal constant; some teams require significantly more history before trusting a per-user baseline at all. Communicate to business stakeholders up front that any fixed-sigma rule WILL produce some false positives by design (that's the nature of a statistical threshold), and pair the flag with a simple mechanism for a reviewer to mark a specific alert as "not actually anomalous," feeding that feedback back into future threshold tuning rather than silently living with noise.

Direct answer

You measure whether your communication actually works the same way you'd measure any other process: define what 'acted on' looks like concretely, instrument it, and track it over time, rather than assuming a well-received presentation equals a changed decision.

Structured elaboration

1. Separate 'insight was delivered' from 'insight was acted on.'
Most teams only track the former (a deck was presented, a dashboard exists) because it's easy to observe. The real signal is whether a decision, a roadmap item, or a resourcing choice actually changed as a result. That requires deliberately logging each insight or recommendation as a discrete, trackable unit (a ticket, a decision-log entry, a recommendation ID) rather than letting it live only inside a slide deck that nobody revisits.

2. Define 4-6 concrete metrics that make actionability observable.
A reasonable, non-exhaustive set: (a) share of recommendations formally accepted, rejected, or deferred within a defined window (e.g. 30 days) - the acceptance rate; (b) average time from delivery to a decision being made on it - time-to-decision; (c) share of accepted recommendations that were actually implemented, not just approved - the follow-through rate, since approval without implementation is a common failure mode; (d) measured downstream business impact where an accepted recommendation included a predicted effect (did the metric move the way the insight predicted, and by how much); (e) a stakeholder-reported usefulness or trust score, gathered periodically, as a leading indicator; and (f) recurrence rate of the same insight being re-delivered because it was previously ignored, which is a strong negative signal.

3. Build the minimal data collection to make this trackable, not a large new system.
In practice this is a lightweight log: each insight gets an ID, a delivery date, an owner, a decision outcome, and (if applicable) a link to the metric it was supposed to move. This can live in an existing ticketing or decision-log tool rather than requiring new infrastructure; the discipline is in the LOGGING HABIT, not the tooling.

4. Assign ownership and a reporting cadence.
The team that produces insights (analytics, data science, BI) should own tracking whether insights were delivered and understood; the business owner who received the recommendation should own logging the decision outcome, since they are the one who knows whether it was actually acted on. Report the rollup on a cadence that matches how often recommendations are made (commonly monthly or quarterly) rather than in real time, since 'time to decision' for a nontrivial recommendation is naturally measured in weeks, not hours.

Worked example

A data science team delivers 40 recommendations over a quarter (for example: adjust a pricing tier, change an onboarding step, retire an underperforming feature). They log each with an ID and owner. At quarter end: 28 of 40 were formally decided within 30 days (70% decision rate), of which 19 were accepted, 6 rejected, and 3 deferred; of the 19 accepted, 14 were actually implemented within the quarter (a 74% follow-through rate on acceptances); and of those 14, 9 had a predicted metric attached, of which 6 moved in the predicted direction by at least half the predicted magnitude. The team also finds that 5 of the 40 recommendations were substantively the same insight delivered a second time because the first delivery was never decided on, a recurrence signal that prompts them to investigate why certain recommendation types stall (in this case, three of the five involved a cross-team dependency with no clear single decision-owner). That specific finding, a missing decision-owner for cross-team recommendations, becomes the actionable process fix, which is itself an example of the framework working as intended.

Trade-offs and pitfalls

• The biggest pitfall is conflating 'stakeholders liked the presentation' with 'a decision changed'; a positive reaction in the room is not evidence of actionability and should not substitute for the follow-through metrics above.
• Attributing a downstream metric move entirely to one recommendation is often overclaiming, since other changes happen concurrently; where possible, treat the predicted-impact check as a directional signal, not a rigorous causal claim, and say so.
• A high recurrence rate is more informative than a low acceptance rate; recommendations legitimately get rejected for good reasons, but a recommendation that keeps resurfacing because no one ever decided on it points to a process gap, not a communication gap.
• Do not build a heavy new tracking system before establishing the logging habit manually; teams that try to automate this before anyone consistently logs decisions end up with clean-looking dashboards over incomplete data.

Direct answer: Define the primary metric as click-to-purchase conversion rate for sessions that reach the one-click checkout option, measured as completed purchases divided by sessions where the option was shown, over a 14-day rolling window per user cohort, chosen because it directly isolates the friction-reduction mechanism the feature is meant to deliver, and it can be gamed only by artificially inflating impressions of the option, which is separately monitorable.

Structured elaboration

1. Metric definition (numerator/denominator): numerator is completed purchases where the one-click option was shown and available; denominator is sessions where the option was shown and available. This isolates the effect of the feature being present, rather than diluting it with sessions where users never saw the option at all.
2. Unit of analysis: session, not user, because a single user can have multiple shopping sessions with different intents, and the feature's effect on any given checkout attempt is what the metric should capture; a per-user metric would need additional handling for repeat exposure that is not necessary here.
3. Measurement window: 14 days, long enough to capture users who browse before returning to purchase, short enough that the metric stays close to the feature's actual causal window rather than absorbing unrelated later behavior.
4. Alignment and gaming risk: the metric aligns with the business goal (reducing checkout friction should directly raise this ratio); it could be gamed by showing the one-click option more aggressively to sessions already very likely to convert (cherry-picking exposure), which would inflate the ratio without the feature doing any real work, so exposure-rate and exposure-eligibility criteria need their own separate monitoring to catch this.
5. Instrumentation required: an event when the one-click option is shown (with a session and eligibility flag), and an event when a purchase completes, joined on session ID, plus a flag distinguishing one-click completions from standard-flow completions in the same session to allow a clean before/after or exposed/unexposed comparison.

Worked example: Baseline standard-checkout conversion (before the one-click option existed) was 22% of sessions reaching checkout. After launch, sessions where the one-click option is shown convert at 28%, a real 6-point absolute lift. Checking the gaming risk: the option's exposure rate among all checkout-reaching sessions is 61%, consistent with its intended eligibility rules (users with a saved payment method), not concentrated suspiciously in already-high-intent sessions, so the lift is judged genuine rather than an artifact of selective exposure.

Trade-offs and pitfalls: Defining the denominator as ALL checkout-reaching sessions, rather than only those where the option was shown, would dilute the metric and understate the feature's real effect on the population it actually touches; the more common failure, though, is the opposite: defining the metric so narrowly that it can be inflated by narrowing WHO gets shown the option, which is exactly the gaming risk named above and requires its own guardrail to catch.

Direct answer: Writing the retention common table expression (CTE, a named WITH-clause subquery) and window-function pipeline is the easy 20%. At hundreds of millions of users, running it from raw events every night is the part that doesn't survive contact with production: the fix is to pre-aggregate raw events into a compact per-user-week activity table, partition that table (and the final matrix) by cohort or activity week so a write only ever touches the weeks it affects, and materialize the 12x12 retention matrix as a small table refreshed incrementally rather than recomputed from scratch. The two-sequential-event cohort assignment (signup, then a later onboarding-completed) and late-arriving backfill are really the same design problem in miniature: both require the pipeline to know precisely which slice of the matrix a given late-arriving event can possibly affect, so it only recomputes that slice.

Structured elaboration

Why nightly-from-raw-events doesn't scale: a retention matrix is a slow-moving key performance indicator, or KPI (it changes by a handful of new signups and a week's worth of new activity each night), but a from-scratch pipeline pays for scanning the entire multi-hundred-million-row, multi-week history every single run, more than 99% of which is unchanged from the previous night. The fix is incremental processing on top of a pre-aggregated, partitioned base table, not a faster version of the same full scan.

Performance strategy, in order of impact:

1. Pre-aggregate first. Collapse raw events (many rows per user per day) into user_week_activity(user_id, activity_week) once. This is the single biggest cardinality reduction: bounded by users x weeks, not users x events.
2. Partition by the dimension that changes. New activity this week affects every past cohort's retention row simultaneously (a user from a 10-week-old cohort being active this week updates that cohort's week-10 column), so partition/cluster user_week_activity by activity_week, and only append this week's slice. The matrix update for a given cohort_week + week_offset cell then follows directly from who was active in the newly-appended activity week and what their cohort_week was, which only requires touching users active that week, not the full user base.
3. Materialize the matrix as its own small table. 12 cohorts x 12 offsets is at most ~144 rows; refresh it with a MERGE/upsert against the incremental user_week_activity delta, and let dashboards read the small table, never the raw events.
4. Reserve sampling for exploration only. Approximate counts (e.g. HyperLogLog-style distinct estimators, a family of algorithms that trade a small, bounded error for counting distinct users in a fraction of the memory an exact count would need) are fine for an analyst poking at trends, but a nightly authoritative retention number is exactly the kind of metric (often reported externally or to leadership) where "approximately right" silently erodes trust if it doesn't reconcile with billing or engagement counts elsewhere.

Two-sequential-event cohort assignment (signup, then onboarding-completed): a cohort here isn't a single timestamp, it's the resolution of two ordered events for the same user. A LATERAL join expresses this cleanly:

WITH signups AS (
  SELECT user_id, MIN(event_time) AS signup_time
  FROM events WHERE event_type = 'signup'
  GROUP BY user_id
)
SELECT s.user_id, s.signup_time, ob.onboarding_time,
       DATE_TRUNC('week', ob.onboarding_time) AS cohort_week
FROM signups s
JOIN LATERAL (
  SELECT MIN(e.event_time) AS onboarding_time
  FROM events e
  WHERE e.user_id = s.user_id
    AND e.event_type = 'onboarding_completed'
    AND e.event_time >= s.signup_time
) ob ON ob.onboarding_time IS NOT NULL
ORDER BY s.user_id;

The ON ob.onboarding_time IS NOT NULL turns this into an inner-join-like filter: a user who signed up but never completed onboarding gets no cohort assignment at all, which is correct (they can't be placed on a retention matrix keyed by a week they never reached). A plain window-function alternative (e.g. a filtered MIN() OVER, or LEAD with a type filter) works too; LATERAL makes the "find the qualifying later event" condition explicit and lets an index on (user_id, event_type, event_time) serve it directly. This is a meaningfully different, stricter rule than a simpler daily first-touch cohort assignment (cohort_week = the week of a user's very first event of any kind, with no second qualifying event required); first-touch assignment never has an "unassigned" user, while the two-sequential-event rule always will, for anyone who never reaches step two, and that population needs to be tracked and reported on its own, not silently folded into "week 0 retention."

Late-arriving events and backfill without a full recompute: the failure mode to avoid is treating "backfill" as "rerun the whole pipeline." Instead, track which cohort_week partitions a batch of newly-arrived events actually touches, and MERGE only those:

• A late activity event for an existing cohort only dirties one (cohort_week, week_offset) cell: the user's own cohort_week and the offset implied by the late event's own week.
• A late onboarding_completed event is more dangerous, because it can retroactively change which cohort a user belongs to (a user with no prior cohort assignment now gets one, or one assigned to a wrong/placeholder cohort now moves). The dirty set for that user is the union of their old cohort_week (if any) and their new one; recompute both, not just the partition the late event's own timestamp falls in.
• A per-batch "touched cohort_weeks" list (derived from DATE_TRUNC('week', <the relevant event time>) on just the newly-arrived rows) is cheap to compute and is exactly what should drive the MERGE's WHERE/target-partition scope.

Worked example (executed in DuckDB)

Three events for two users: user 1 signs up 2026-01-01, completes onboarding 2026-01-03; user 2 signs up and completes onboarding both on 2026-01-02; user 3 signs up 2026-01-05 but never completes onboarding. Running the LATERAL cohort query above returns exactly two rows (users 1 and 2, both assigned cohort_week = 2025-12-29, the Monday-starting week containing their onboarding completion), and correctly omits user 3. A late-arriving onboarding event for user 2 at 2026-01-02 20:00 resolves to DATE_TRUNC('week', ...) = 2025-12-29, giving exactly the one dirty partition that a backfill job would need to re-MERGE, not the whole table.

Trade-offs & pitfalls

• Partitioning only by cohort_week (and not also by activity_week) is a common half-measure: it makes backfilling a mis-assigned cohort cheap, but doesn't help the every-night append of new activity, which is naturally keyed by activity_week instead.
• A recursive common table expression (recursive CTE, one that refers to itself to build a result iteratively; Postgres requires the RECURSIVE keyword explicitly, SQL Server does not) can generate the 0..11 week-offset series inline instead of a stored numbers/calendar table, but a small reusable calendar table is usually the better production choice: it's index-friendly and shared across every query that needs a week series, where a recursive CTE regenerates the same series from scratch each time it's used.
• Forgetting to widen the dirty-partition set to include a user's previous cohort_week when a late onboarding event reassigns them is the single most common backfill bug in this pattern: it leaves a stale, too-high retention count sitting in the old cohort's row.

Direct answer. A funnel query needs to answer two related but distinct questions per step: what fraction of users convert to the next step (localizing WHERE users are lost), and how LONG it takes them to get there (localizing whether the experience itself has gotten slower, even when the eventual conversion rate looks unchanged); a single query can compute both, and the two can tell very different stories about the same funnel.

Structured elaboration. For conversion, take the first occurrence of each step event per user per cohort, count distinct users at each step, and compute the ratio between consecutive steps; users who skip a step or arrive out of order need an explicit rule (usually: only count a step as reached if its timestamp is at or after the previous step's timestamp for that user, dropping or flagging out-of-order sequences rather than silently mis-ordering them). For velocity, join each user's timestamp at one step to their timestamp at a later step and compute the time difference, then take the MEDIAN (and optionally the 90th percentile) per cohort rather than the mean, since time-between-steps is typically right-skewed by a small number of very slow or stalled users. Missing timestamps should exclude that user from the velocity calculation for that specific step pair without dropping them from the conversion-rate calculation, since the two metrics have different data requirements.

Worked example (executed in DuckDB against a synthetic dataset). Two signup cohorts one week apart, both similar in size (500 users each): the earlier cohort's activation-to-purchase conversion rate is 68.9%, the later cohort's is actually slightly HIGHER at 73.4%, a conversion-rate-only view would show no regression at all, arguably a mild improvement. But median activation-to-purchase VELOCITY goes from 1.49 hours in the earlier cohort to 8.48 hours in the later cohort, a 469% slowdown. The conversion rate alone completely misses a real, severe regression in how long the experience takes, which is exactly why this topic treats velocity as a distinct, necessary signal rather than a secondary detail: a synthetic delay injected into the later cohort's purchase timing (representing, for example, a slower payment flow) doesn't show up in the eventual conversion rate at all, only in the time-to-convert.

Trade-offs and pitfalls. Reporting only conversion rate on a funnel dashboard, the far more common default, can leave a real, user-visible regression completely invisible until it eventually shows up downstream as lower same-day revenue or worse near-term retention, by which point it's much harder to trace back to its root cause; a velocity panel alongside every conversion-rate panel closes that blind spot directly.

Direct answer

Put the date-range condition in the ON clause, not WHERE. That keeps every account in the result, even ones with zero matching transactions in the range, as a NULL-padded row from the LEFT JOIN, which then gets COALESCEd to $0. A date filter in WHERE runs after the join and discards any account whose transactions (matched or NULL-padded) don't satisfy it, silently dropping the account from the report entirely rather than showing it at $0.

Structured elaboration

Approach: this is the same root mechanism as filtering a right-hand table's column in WHERE after a LEFT JOIN (predicate applied post-join kills NULL-padded rows), applied here to a date range instead of an equality filter. The visible symptom is worse for this specific ask: it's not that a matched row loses some detail, the account disappears from the report completely, which directly violates the requirement that zero-activity accounts must show up at $0.

Key points:

• Date range in ON: narrows which transactions count as a match, but every account still survives the join.
• Date range in WHERE: filters the combined, already NULL-padded rowset; an account with no transactions in range (whether it has transactions elsewhere, or none at all) fails the WHERE test and vanishes.
• A predicate on the LEFT table itself (accounts), like WHERE a.active = TRUE, is a different case and belongs in WHERE; don't over-correct by moving every predicate into ON.

Worked example

Seed data: 4 accounts. Acme Co has a transaction inside the range. Beta LLC has a transaction, but only outside the range. Cobalt Inc has no transactions at all, ever. Delta Group has two transactions inside the range.

Buggy, date filter in WHERE:

SELECT
  a.account_id, a.account_name,
  COALESCE(SUM(t.amount_cents), 0) / 100.0 AS revenue_usd
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE t.transaction_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY a.account_id, a.account_name
ORDER BY a.account_id;

Result (run in DuckDB):

account_id | account_name | revenue_usd
-----------+--------------+------------
         1 | Acme Co      |       100.0
         4 | Delta Group  |        50.0

Beta LLC and Cobalt Inc are simply gone. This is easy to miss in review, since the query "runs fine" and returns a plausible-looking table, just a shorter one than it should be.

Fixed, date filter moved into ON:

SELECT
  a.account_id, a.account_name,
  COALESCE(SUM(t.amount_cents), 0) / 100.0 AS revenue_usd
FROM accounts a
LEFT JOIN transactions t
  ON t.account_id = a.account_id
  AND t.transaction_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY a.account_id, a.account_name
ORDER BY a.account_id;

Result:

account_id | account_name | revenue_usd
-----------+--------------+------------
         1 | Acme Co      |       100.0
         2 | Beta LLC     |         0.0
         3 | Cobalt Inc   |         0.0
         4 | Delta Group  |        50.0

All 4 accounts appear. Beta LLC and Cobalt Inc correctly show $0.00 in the range, distinguishing "no activity in this window" from "doesn't exist," which is exactly what the requirement asked for.

Complexity

Both versions are a single scan of accounts with an index (or hash) lookup into transactions, O(n + m) with an index on transactions.account_id. The fix changes correctness, not complexity class; on most cost-based planners, a selective date predicate in ON can be pushed into the scan of transactions before the join, so there is no performance downside to the fix.

Edge cases

• An account whose only transactions fall entirely outside the range (Beta LLC): correctly shows $0.00, distinct from Cobalt Inc, which has no transactions ever. Both look identical in this report's output, which is fine for a revenue report but worth flagging if a downstream consumer needs to tell "inactive this quarter" apart from "never had an account with us."
• A predicate on accounts itself, such as excluding closed accounts, is a filter on the LEFT table and belongs in WHERE, not ON; conflating the two rules (all predicates move to ON) is itself a mistake.
• Timezone-naive date columns: if transaction_date is actually a timestamp with time components, a naive BETWEEN on date strings can silently exclude the last partial day of the range; cast explicitly or use half-open bounds (>= start AND < end + 1 day).

Trade-offs & pitfalls

• The failure mode here (accounts disappearing entirely) is more dangerous to catch in review than the S35-style version (a matched row losing detail), because the query returns a shorter, still-plausible-looking table with no error and no obviously missing row to spot; validating against COUNT(*) FROM accounts as an expected row-count floor is a cheap habit that catches it immediately.
• Resist "fixing" this by adding OR t.id IS NULL back into WHERE; it happens to work for a single date-range predicate on one column but breaks the moment a second filtered join is added to the same query.
• If a downstream consumer needs to distinguish "no activity in range but has history elsewhere" from "never had any transactions," this query as written can't do that without an additional column (e.g., a separate EXISTS check against unfiltered transactions); decide whether that distinction matters before shipping the simpler version.

Direct answer
A one-page executive dashboard should fit a single screen with 5-8 top-level KPIs, a trend sparkline for each, a small set of high-value filters (time range, segment), and a compact table or list surfacing the top anomalies, laid out so the most important number is the first thing the eye lands on and deeper detail is one click away.

Structured elaboration

1. Metric selection: choose the smallest set of KPIs that together tell the state of the business (for a monthly business review: revenue, gross margin, a growth-rate metric, a health/quality metric, and a forward-looking indicator like pipeline or churn risk).
2. Visual hierarchy: the single most important number goes top-left (where eyes land first in most reading patterns) or top-center; sparklines accompany each KPI tile so trend is visible without opening a separate chart.
3. Filters: keep to 1-2 global filters (commonly time range and one segmentation dimension like region or product); more than that belongs in a secondary/analyst view, not the executive one-pager.
4. Anomaly surfacing: a small table listing the 3-5 largest unexplained deviations (metric, expected vs. actual, magnitude) turns the dashboard from passive reporting into an action-prompting tool.
5. Color and layout for executive consumption: use color sparingly and only to signal status (on-track / at-risk), not decoratively; keep the grid consistent so tiles align, and reserve the bottom or a secondary tab for drill-down detail.

Worked example
A subscription SaaS growth dashboard: MRR (Monthly Recurring Revenue, the predictable subscription revenue expected each month), new-vs-churned revenue, activation rate, NPS (Net Promoter Score, a customer-loyalty survey metric scored from -100 to +100 based on how likely customers are to recommend the product), and a trailing-12-month LTV/CAC ratio (customer Lifetime Value divided by Customer Acquisition Cost, i.e. whether a customer is worth more over their lifetime than it cost to acquire them), each with a sparkline, one date-range filter, and an anomalies table flagging any metric that moved more than one standard deviation from its trailing baseline. Layout position follows importance: MRR (the headline number) top-left, new-vs-churned revenue top-center, activation rate top-right, NPS and LTV/CAC on a second row, and the anomalies table spanning the bottom of the page.

Trade-offs and pitfalls
The temptation to add "just one more metric" compounds quickly into a cluttered page; treat every proposed addition as displacing something else, since a true one-pager has a hard ceiling on how much it can show before it stops being scannable in under a minute.

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.

Approach: generate the last 12 full months as a date series, left-join aggregated transactions (excluding NULL amounts and cancelled), compute total amount and average per active user (sum(amount) / count(distinct user_id)). Use date_trunc to bucket by month and ensure months with zero transactions appear.

WITH months AS (
  -- last 12 full months (not including current partial month)
  SELECT generate_series(
           date_trunc('month', current_date) - interval '12 months',
           date_trunc('month', current_date) - interval '1 month',
           interval '1 month'
         )::date AS month_start
),
tx AS (
  SELECT
    date_trunc('month', occurred_at)::date AS month_start,
    amount,
    user_id
  FROM transactions
  WHERE amount IS NOT NULL
    AND status <> 'cancelled'
    AND occurred_at >= (date_trunc('month', current_date) - interval '12 months')
    AND occurred_at < date_trunc('month', current_date)
)
SELECT
  m.month_start,
  COALESCE(t.total_amount, 0)                     AS total_amount,
  COALESCE(t.total_amount / NULLIF(t.active_users,0), 0) AS avg_amount_per_active_user
FROM months m
LEFT JOIN (
  SELECT
    month_start,
    SUM(amount) AS total_amount,
    COUNT(DISTINCT user_id) AS active_users
  FROM tx
  GROUP BY month_start
) t USING (month_start)
ORDER BY m.month_start;

Key points:

• generate_series creates continuous months so months with zero activity still show.
• Excluding current partial month ensures "full months."
• NULLIF prevents division by zero; COALESCE returns 0 when no data.
• For large tables, ensure index on occurred_at (and status) and consider pre-aggregating or using materialized views for dashboard performance.

Direct answer

Structuring this conversation means showing the actual trade-off explicitly, how much conversion moves versus how much retention moves, and over what time horizon each pays off, rather than letting the debate stay abstract; the recommendation then depends on which horizon the business can afford to prioritize right now, not on which metric sounds more urgent in the room.

Structured elaboration

The first step is to make sure both sides are looking at comparable evidence: pull the historical or experimental relationship between the specific onboarding elements under debate and each of the two metrics, since "prioritize conversion" and "prioritize retention" often are not actually in tension for every possible onboarding change, only for the specific ones being proposed. Where they genuinely trade off, the useful visualization is a simple two-axis comparison: short-term conversion lift on one axis, medium-term retention impact on the other, for each candidate onboarding change under discussion, so stakeholders can see which changes are win-win, which are a genuine trade-off, and which help neither.

For changes that are a genuine trade-off, the recommendation should hinge on an explicit, quantified comparison of value over time: a conversion gain realized immediately versus a retention gain that compounds over a longer horizon (since a retained user keeps generating value repeatedly, while a one-time conversion does not) usually favors retention when the horizon is long enough, but that conclusion depends on the business's actual runway and near-term revenue needs, which is a business input the data alone cannot supply, so naming that dependency explicitly to the room is part of a well-structured recommendation, not a hedge.

Worked example

Suppose a proposed onboarding change is projected to lift conversion by 2 percentage points immediately but reduce 90-day retention by 4 percentage points relative to the current flow, based on a completed A/B test. If the product monetizes primarily through a one-time purchase, the immediate conversion lift may dominate, since there is little future retained-user value being given up. If instead the product monetizes through an ongoing subscription where a retained user generates revenue every month, the 4-point retention loss compounding over many months of subscription revenue is very likely to outweigh a one-time 2-point conversion gain, and laying out that monthly-compounding comparison explicitly, rather than debating "conversion versus retention" as two competing slogans, is what turns the disagreement into a decision.

Trade-offs and pitfalls

The most common failure in this kind of conversation is treating it as a philosophical debate about which metric matters more in general, rather than a specific, quantifiable trade-off for the specific change on the table; grounding the discussion in the actual measured or projected impact of THIS change avoids that trap. It is also worth flagging explicitly when the data does not yet exist to make the trade-off precise (for example, only a short-term A/B test result is available, with no data yet on whether the retention impact persists past 90 days), rather than presenting a confident recommendation built on an extrapolation nobody has validated.