Netflix Data Analyst Interview Preparation Guide – Junior Level

Direct answer

For a pipeline ingesting on the order of a million events per minute, the concrete operational metrics to instrument are ingestion lag/freshness, row-count deltas against an expected baseline, per-column null-rate spikes, schema-change events, and duplicate rate, each with a starting severity tier that distinguishes a real regression from ordinary day-to-day variance.

Structured elaboration

• Ingestion lag: time between event occurrence and it landing queryably; alert if lag exceeds a threshold sustained for several consecutive minutes, not on a single spike, to avoid paging on a transient blip.
• Row-count delta: compare the current window's row count against a rolling baseline (a trailing 7-day median for the same time-of-day/day-of-week, not a flat average, since volume has strong seasonality); alert on a large relative deviation, for example more than 3x the typical day-to-day variance.
• Null-rate spike: track per-column null rate over time and alert when a critical column's null rate jumps materially above its historical baseline, since a sudden jump is a much stronger signal of a real upstream break than a stable-but-nonzero baseline null rate.
• Schema-change events: alert on any unexpected added, removed, or type-changed column, since even a seemingly benign addition can break a strict downstream consumer.
• Duplicate rate: track the rate of exact or near-duplicate records and alert on a sustained increase, which is usually a symptom of a retry storm or a broken idempotency guarantee upstream.

Worked example

At this ingestion volume, a severity tiering that works well in practice: freshness lag over 5 minutes = warning (page only if sustained 15+ minutes), row-count delta over 3x the typical day-of-week variance = page immediately, any unexpected schema change on a table with more than 5 known downstream consumers = page immediately, null-rate spike over 5 percentage points above the 7-day baseline on a required field = page, duplicate rate above 2x baseline = warning.

Trade-offs and pitfalls

The recurring failure mode at this scale is comparing against a flat, non-seasonal baseline: a naive "alert if today's count differs from yesterday's by more than X%" fires constantly on legitimate day-of-week seasonality (weekends are naturally lower volume for most consumer products) and trains the on-call team to ignore the alert channel entirely. A seasonality-aware baseline, even a simple trailing-median-by-day-of-week, removes most of that noise and is usually a bigger improvement to signal quality than tightening or loosening the raw threshold.

Direct answer: Compute the net daily revenue by multiplying visitors by conversion rate by average order value in each state and comparing the two totals; here, the feature is net positive, generating an estimated $880,000 more per day, because the relative gain in conversion rate (20%) outweighs the relative drop in average order value (2%).

Structured elaboration

• Revenue per day is the product of three quantities: total visitors, conversion rate, and average order value (AOV). Since visitors are held constant here, comparing before and after isolates the combined effect of the conversion-rate change and the AOV change.
• $\text{Revenue}_{\text{before}} = 1{,}000{,}000 \times 0.10 \times \$50 = \$5{,}000{,}000$
• $\text{Revenue}_{\text{after}} = 1{,}000{,}000 \times 0.12 \times \$49 = \$5{,}880{,}000$
• $\Delta \text{Revenue} = \$5{,}880{,}000 - \$5{,}000{,}000 = \$880{,}000 \text{ per day (+17.6\%)}$
• The intuition behind why a smaller-looking AOV drop (2%) is dominated by a larger-looking conversion gain (20% relative) is that the conversion improvement applies to the ENTIRE new set of purchasers created by the higher rate, while the AOV drop applies only to the (larger) pool of orders now happening; multiplying it out is what resolves the apparent tension rather than eyeballing the two percentages against each other.
• Before declaring this a clean net-positive result, state the assumptions: this assumes the conversion-rate and AOV changes are both statistically real (not noise) and causally attributable to the feature (not, say, a concurrent promotion), and that the AOV drop will not compound over time (e.g., if the feature specifically nudges people toward cheaper items, watch whether AOV keeps sliding in subsequent weeks rather than stabilizing at the new $49 level).

Worked example: If the AOV drop had instead been to $41 (an 18% relative drop) rather than $49, the same calculation would flip the sign: $1{,}000{,}000 \times 0.12 \times $41 = $4{,}920{,}000$, an $80,000 daily LOSS versus baseline, despite the same 20% relative conversion gain. This shows why the "is it net positive" question cannot be answered by comparing the two percentage changes directly; the actual dollar multiplication is required, and a seemingly large conversion win can still be a net loss if the AOV drop is large enough.

Trade-offs and pitfalls: The most common mistake is eyeballing "conversion up more than AOV down, so it must be a net win" without doing the multiplication, which happens to work in this exact example but is not reliably true in general, as the alternate scenario above shows. The other pitfall is treating the point-in-time $880,000 figure as a permanent daily run-rate without checking whether the underlying rates are stable or still shifting (a common issue if the feature is new and still ramping or subject to a novelty effect).

Direct answer

Beyond CTR (click-through rate), a healthy recommender needs signals for whether users are genuinely satisfied, whether they keep returning, and whether the catalog they see stays diverse rather than collapsing onto a shrinking set of popular items. A useful set covers retention, engagement quality, diversity, novelty, explicit satisfaction, and the downstream business outcome the recommender exists to serve. Feedback loops are detected by checking whether rising engagement on these input signals is paired with falling quality on outcome signals over a longer horizon than the model's own feedback window.

Structured elaboration

Detecting harmful feedback loops

• Concentration tracking: monitor exposure concentration with the Gini coefficient (a 0-to-1 measure of how unequally impressions are spread across items, 0 is perfectly even, 1 is all impressions on one item) or category entropy over time; rising concentration alongside falling diversity is the classic signature of a popularity loop, where popular items get recommended more because they were recommended more.
• Lagged correlation: correlate this period's exposure concentration against next period's retention and engagement quality, not the same period's, since the damage from a loop shows up with a lag.
• Held-out exploration: keep a small, lightly personalized slice of traffic as a floor, and compare its long-horizon retention against the fully personalized slice.
• Offline counterfactual checks (an advanced technique, beyond what most interviews expect in depth): inverse propensity weighting (reweighting logged interactions to estimate what a different policy would have produced) can flag a policy that looks good on logged CTR but would underperform under a different exposure distribution.

Worked example

Illustrative, hypothetical figures across two weekly snapshots:

$\text{CTR relative change} = \frac{6.2\% - 5.0\%}{5.0\%} = 24\%\text{ increase}$
$\text{Retention relative change} = \frac{42\% - 36\%}{42\%} \approx 14.3\%\text{ decrease}$
$\text{Concentration change} = 35\% - 20\% = 15\text{ percentage points}$

CTR and concentration are both climbing while the retention of the recommender's heaviest users is falling, exactly the pattern a CTR-only dashboard would report as a win. Reading concentration and lagged retention alongside CTR catches the loop before it shows up in company-level revenue.

Trade-offs & pitfalls

• Every added metric has a monitoring and alerting cost; proposing ten metrics with no owner or cadence for any of them is a common senior-sounding but hollow answer. Pick the smallest set that actually changes a decision.
• Entropy and Gini-based diversity metrics can be gamed by injecting low-quality items just to raise the number; pair diversity metrics with a satisfaction or retention guardrail.
• Offline counterfactual methods reduce, but do not eliminate, bias from confounding; treat them as a prioritization signal for what to A/B test, not a replacement for the online test.
• Long-horizon metrics (30- to 90-day retention) are most likely to catch a feedback loop but are also slowest to move; pair them with a faster leading indicator such as weekly concentration or entropy.

Direct answer

Eventual consistency means that after writes stop, all copies of the data will eventually agree, but there's a window, sometimes milliseconds, sometimes longer, during which different readers can see different, both "correct at the time" answers. For a non-technical stakeholder, the useful line is: the system prioritizes staying responsive everywhere over making everyone see the same thing at the exact same instant. Below are three analogies for that idea, each with the one place it will mislead if you don't say it out loud.

Choosing the analogy and what to omit

• Pick an analogy where the delay AND the reconciliation are both visible, not just the delay. Many weak analogies (mail, gossip) only show that news travels slowly; they hide the harder part, what happens when two people acted on different information during that delay.
• Decide up front which mechanism you're omitting: you're almost always omitting HOW the system decides which write wins when two conflict. Say that you're leaving it out, rather than letting the analogy imply there's no rule for it at all.
• Check understanding by asking them to predict a scenario, not recite the definition back: "if two people edit this at the same moment from different offices, what do you think happens?" A correct prediction means the model landed; an answer that assumes instant sync means you need to go back to the delay itself.
• The same shape, plain definition, one concrete example, why it matters, holds for any jargon-heavy term a non-technical audience needs defined on the spot: ETL vs ELT (does the transformation happen before or after loading), ACID vs BASE (strict correctness vs eventual, available correctness, which is this same idea from the database's side), or REST vs GraphQL (fetch a fixed shape of data vs ask for exactly the fields you need). Same competency, different vocabulary each time.

Worked example

1. A group chat where one person's phone is off. You send a message to a group chat; everyone online sees it in under a second. Someone whose phone died an hour ago won't see it until they turn it back on, at which point it downloads and they're caught up. What it shows well: the "everyone gets there eventually, but not at the same time" shape, and that being offline doesn't break the system, it just delays that one reader. Where it misleads: it implies messages simply queue up in order. If two people update the SAME piece of shared data while a third is disconnected, there can be a genuine conflict to resolve, not just a backlog to deliver, and the chat analogy has no equivalent of "two people edited the same message."

2. A retail chain updating a sale price across stores. Head office cuts a price. Each store's system checks for updates on its own schedule, so for a few minutes Store A shows the new price and Store B still shows the old one. What it shows well: the same data existing in multiple places, each catching up on its own timeline, with no single moment where everyone updates at once. Where it misleads: it suggests the only direction of change is head office to stores, one writer, many readers. Real eventually consistent systems often allow writes at multiple locations at once, a customer changing their address from two devices, and that's where the interesting conflicts and reconciliation rules actually come from.

3. Watering one end of a long garden bed. You water one end of a dry garden bed and moisture visibly spreads down the row over the next hour until it's evenly damp. What it shows well: gradual, automatic convergence toward one final state with no single "sync" event. Where it misleads: soil moisture always converges smoothly. Some real systems can get stuck in a genuine conflict that never resolves on its own, two writes with no way to tell which should win, and need a rule, or a human, to break the tie. "It'll just even out" is the sentence most likely to leave a stakeholder with a false sense of safety.

Trade-offs and pitfalls

The single biggest risk in any of these analogies is implying the temporary disagreement is harmless. For some products it is, a slightly stale follower count. For others it isn't, two systems both believing they hold the last unit of inventory. Say plainly which case you're in. Also resist stacking all three analogies in one conversation; one that survives a follow-up question beats three shallow ones, use the extra two only if the first one visibly didn't land.

Direct answer
A 12-week revenue forecast for finance needs: a clear specification of inputs and features, a short list of candidate model classes matched to the data's size and structure, a rolling-origin backtest to validate them, an explicit way to present uncertainty (not just a point number), and a defined deployment/monitoring plan so the forecast stays trustworthy after handoff.

Structured elaboration

• Data inputs and features: the historical revenue series itself at the right granularity (weekly, to match the 12-week ask), plus any known drivers over the forecast window - planned promotions, pricing changes, seasonality (day-of-week/month effects), and macro or pipeline signals if available (e.g. sales-pipeline coverage for a B2B business).
• Model classes to consider: for a single well-behaved series with a year or more of clean weekly history and some seasonality, ETS or SARIMA are strong, quick, interpretable starting points; if there are meaningful external drivers (promotions, pricing), SARIMAX or a gradient-boosted model on engineered features can incorporate them; if this is one of many similar revenue lines, a pooled/global model trained across all of them usually outperforms any single-series model.
• Validating with time-series cross-validation: backtest with rolling-origin evaluation at the SAME horizon you'll actually deploy (12 weeks), not a shorter proxy horizon, since forecast error typically grows with horizon and a model validated at 1-step-ahead can look artificially strong.
• Presenting uncertainty to stakeholders: never hand finance a bare point number; show a prediction interval (from residual-based, bootstrap, or quantile methods) and, ideally, a short plain-language explanation of what's driving the width (e.g. "wider in week 10-12 because we're extrapolating further from known data").
• Deploying and monitoring: define a retraining cadence (e.g. refresh weekly as actuals arrive), track realized forecast error against the backtested expectation, and set an explicit escalation trigger if realized error meaningfully exceeds the backtested distribution (a sign the underlying pattern has shifted).

Worked example
Demand forecasting more broadly answers business questions like supply/staffing planning, and the KPIs and horizons you report should match the DECISION being made, not just "what's easy to compute" - a same-day operational decision needs an hourly KPI, while a quarterly planning decision needs a weekly or monthly one. For a 12-week revenue number specifically: report the point forecast alongside the 80% interval, a one-line explanation of the biggest assumption (e.g. "assumes the current promotion cadence continues"), and the model's own historical accuracy at a 12-week horizon so finance can calibrate how much to trust it.

Trade-offs & pitfalls
Presenting model assumptions and limitations honestly to a non-technical audience matters as much as the model itself: lead with 3-5 concrete assumptions in plain language (e.g. "assumes no new competitor launches", "assumes the same seasonal pattern as last year"), because finance stakeholders act on the number, and unstated assumptions are exactly what turns into "the forecast was wrong" instead of "an assumption we flagged didn't hold." The most common failure mode in this whole workflow isn't model choice, it's backtesting at a horizon that doesn't match production use, which silently overstates how good the deployed forecast will actually be.

Direct answer

A retro after a release with repeated design-engineering friction should produce two things: an honest, specific account of where the handoff actually broke down, not a vague 'communication issues,' and a small number of concrete process changes, each with an owner and a way to tell in a quarter whether it worked. Running it well means separating fact-finding from diagnosis, and diagnosis from blame.

Structured elaboration

Design principles for the session

• Facts before diagnosis: start from a timeline of what actually happened (spec dates, handoff dates, bug counts, points where implementation and design diverged), not from opinions about who was at fault.
• Root cause, not the nearest symptom: 'engineering didn't follow the spec' is a symptom; the root cause might be that the spec didn't capture edge-case states, or that both sides were working from different versions of a shared design system mid-migration.
• Few, high-leverage commitments: two or three process changes people will actually do beat ten action items that quietly get dropped.
• Everyone leaves with the same understanding of what changed, not just what went wrong.

A workable structure

One illustrative shape, adaptable to a team's own rhythm:

What 'actually changes how the two teams work' looks like

The output isn't a list of intentions, it's a specific artifact or habit that exists after the meeting and didn't before: a shared checklist embedded in the handoff process, an automated check that catches a class of mismatch before it ships, or a standing short sync during implementation windows. Whatever it is, it needs a way to tell if it worked, not just that it happened.

Worked example

One team's root cause turned out to be that design tokens (colors, spacing values) were maintained in the design tool but hand-copied into code, so drift was inevitable and nobody could tell which side was 'correct' when they disagreed. The concrete fix was an automated export from the design tool into the codebase, checked by both a design reviewer and a frontend reviewer before merge, plus a short recurring sync during active implementation. A quarter later, the team had a real signal that it worked: noticeably fewer visual-mismatch comments on pull requests and less late-stage rework than the release that triggered the retro. The same root-cause pattern shows up in other domains as a hand-copied data contract or config value instead of a design token, so the same fix shape (automate the handoff, add a lightweight check, add a short sync during the risky window) generalizes well beyond design and engineering specifically.

Trade-offs and pitfalls

• A retro that produces ten action items usually produces zero completed ones; prioritizing ruthlessly matters more than being thorough.
• If the room jumps straight to solutions or blame instead of facts first, the real root cause, often structural or tooling-related rather than a person's failure, never surfaces.
• A retro that isn't revisited becomes theater. Put the check-in on the calendar before the room disperses, not as a vague intention afterward.
• Watch for a fix that only addresses this specific release's symptom (a one-off manual double-check) rather than the structural cause; it holds for one cycle and then quietly stops happening.

Direct answer

A staged ramp trades away some of the speed you already earned by winning the A/B test in exchange for several more checkpoints before you are fully committed: each stage is a chance to catch something the original test could not, whether that is an operational failure mode, a rare-but-severe harm, or the discovery that the effect that looked real in the test does not reproduce at full scale. Define traffic stages with explicit hold durations, monitor a primary metric plus a set of guardrails that are broader than "the metric we tested," and separate the rollback decision into an automated fast path for severe, unambiguous harm and a manual path for judgment calls.

Structured elaboration

Ramp stages and hold durations

A typical shape doubles or roughly triples exposure at each stage, holding longer as the audience (and therefore the blast radius) grows:

The same shape applies whether the thing being ramped is a UI feature, a ranking or recommendation model going from 0% to 100% of traffic, or an LLM-based feature, with the stage durations and thresholds tuned to the risk profile of what is shipping.

Metrics to monitor at every stage

Split guardrails into two families, because they fail in different ways and need different tooling:

• Statistical/behavioral guardrails: the metrics you would recognize from the original test (conversion, engagement, retention) plus explicit counter-metrics for known risk areas (refund rate, support ticket volume, complaint rate). For a feature with a specific known risk, such as an LLM feature that could increase toxic or unsafe outputs, this also includes purpose-built heuristic detectors (a toxicity classifier score, a policy-violation flag rate) alongside the statistical significance check, since some harms are rare enough that a pure significance test would not catch them at low traffic.
• Technical/compatibility guardrails: error rates, crash rates, page-load or latency regressions, and compatibility checks for constituencies the statistical metrics do not naturally cover, such as older browser versions or specific device classes that could break in ways a pooled conversion metric would dilute rather than surface.

A guardrail that only looks healthy in aggregate can still be hiding real degradation concentrated in one user subset; where that risk is plausible, break the guardrail metric out by the relevant segment at each stage rather than trusting a single pooled number, since a ramp is exactly the setting where a segment-specific harm should be caught early, before it reaches the full population.

Rollback strategy

Rollback is not a single mechanism. Automated, fast-path rollback covers severe, well-defined breaches (for example, a payment failure rate spiking multiple times over baseline, or a toxicity-detector rate crossing a hard threshold) and should fire without waiting for a human. Manual, judgment-path rollback covers moderate or ambiguous signals (a metric drifting in a concerning direction without yet crossing a hard line, or a rising trend in qualitative complaints) and routes to an on-call owner plus the stage's decision-maker. For features with a known-safe prior state, such as replacing one production model with a new one, define the rollback target explicitly as a fallback to that known-safe baseline rather than assuming "turn the flag off" is always well-defined; for a brand-new capability with no prior version, the fallback is simply disabling the feature.

Stage ownership

Assign who owns the go/no-go call at each stage before the ramp starts, not during an incident. A common split: engineering owns the smoke-test stage (is it technically stable), the experimentation or data science owner and the product owner jointly own the early- and broad-signal stages (is the effect holding up), and a broader stakeholder review gates the move to near-full and full rollout. Writing this down in advance avoids the failure mode where a guardrail breach happens and nobody is clearly authorized to pull the trigger.

Worked example

A recommendation-ranking model that won its offline and online A/B test is ramped as follows: 1% for 24 hours (smoke test on latency and error rate only, since sample size is too small for a reliable metric read), 10% for 5 days (first real read on click-through and downstream engagement, plus a technical guardrail on p99 latency), 50% for 7 days (confirm the online-test effect size is reproducing at scale and check infrastructure load), then 100%. At the 50% stage, if the observed lift is meaningfully smaller than what the original test measured, that is treated as a signal to pause and investigate before continuing, rather than a signal to keep ramping on the strength of the original result alone.

Trade-offs & pitfalls

• Learning speed versus exposure. A fast ramp gets you to a shipping decision sooner and shortens how long the team is tied up on the rollout, but it exposes more users before you have ruled out a risk you have not yet observed; a slow ramp limits exposure and lets qualitative signals (support trends, direct feedback) accumulate, at the cost of a longer time-to-ship and more elapsed calendar time holding the team's attention.
• The efficacy-to-production gap. A feature that showed a clear positive effect in the original controlled test can show little or no effect once fully shipped. Three causes are worth checking specifically: rollout fidelity (is the shipped, ramped version actually identical to the tested variant, or did something change in translation to production), audience-targeting differences (was the tested population representative of the full rollout population, or did the test skew toward an atypical segment), and novelty decay (was the original effect measured mostly during the novelty window and expected to fade). A staged ramp is one of the few tools that catches this gap before full commitment, because each stage is a fresh chance to check whether the effect is reproducing, not just whether nothing is on fire.
• Guardrails that only cover what the test already measured. The whole point of guardrails beyond the primary metric is to catch harms the original test was never designed to detect; a ramp plan that only re-checks the tested metric at larger scale is not actually buying much additional safety.
• Undefined rollback ownership. A rollback plan with thresholds but no assigned decision-maker per stage tends to stall during an actual incident, which defeats the purpose of having automated fast-path criteria in the first place.

Quick answer

With a Beta(1,1) (uniform) prior on the success rate and Binomial data, the Beta family is conjugate to the Binomial likelihood: this means combining a Beta prior with Binomial data always produces another Beta distribution as the posterior, so no numerical integration is needed, the posterior parameters are just the prior's parameters plus the observed successes and failures. With 30 successes out of 100 trials, that gives a posterior mean of about 0.304 and a 95% credible interval of roughly [0.219, 0.396], computed below.

Framework: Beta-Binomial conjugacy

For a Beta$(\alpha, \beta)$ prior and $k$ successes in $n$ trials:

The 95% credible interval is the interval between the 2.5th and 97.5th percentiles of that posterior Beta distribution.

Worked example

$k = 30$, $n = 100$, prior Beta(1,1):

from scipy import stats

k, n = 30, 100
a0, b0 = 1, 1  # Beta(1,1) prior
a_post, b_post = a0 + k, b0 + (n - k)
post_mean = a_post / (a_post + b_post)
ci_lo, ci_hi = stats.beta.ppf([0.025, 0.975], a_post, b_post)
print(f"posterior Beta({a_post},{b_post}), mean={post_mean:.4f}, 95% CI=({ci_lo:.4f}, {ci_hi:.4f})")
# posterior Beta(31,71), mean=0.3039, 95% CI=(0.2190, 0.3961)

Posterior mean $\approx 0.3039$, 95% credible interval $\approx [0.2190, 0.3961]$.

Shifting to an informative prior, Beta(2,2):

from scipy import stats

k, n = 30, 100
a0b, b0b = 2, 2  # Beta(2,2) prior
a_post_b, b_post_b = a0b + k, b0b + (n - k)
post_mean_b = a_post_b / (a_post_b + b_post_b)
ci_lo_b, ci_hi_b = stats.beta.ppf([0.025, 0.975], a_post_b, b_post_b)
print(f"posterior Beta({a_post_b},{b_post_b}), mean={post_mean_b:.4f}, 95% CI=({ci_lo_b:.4f}, {ci_hi_b:.4f})")
# posterior Beta(32,72), mean=0.3077, 95% CI=(0.2231, 0.3992)

Beta(2,2) is still centered at 0.5 but has slightly more concentrated mass than Beta(1,1), so it pulls the posterior mean up very slightly (0.3039 to 0.3077) and narrows the interval marginally. With $n=100$ observed trials, the data dominates either weak prior; the shift is small because both priors are only worth 2-4 pseudo-observations against 100 real ones.

Credible interval vs. confidence interval

The Wald interval, computed from $\hat p = 0.30$, $SE = \sqrt{0.30 \times 0.70 / 100} \approx 0.0458$, gives $[0.210, 0.390]$, close to the Beta(1,1) credible interval because a flat prior contributes almost no information beyond what the data already says. This convergence is the general pattern: with weak priors and reasonably large $n$, credible and confidence intervals often land close together numerically, even though what they claim to mean is fundamentally different.

Trade-offs and pitfalls

• The 95%-probability statement is only valid for the credible interval, not the confidence interval. Interviewers often listen for whether a candidate slips and says "95% probability the true rate is in this range" about a frequentist CI; that statement is only correct for the Bayesian interval.
• A subjective prior invites scrutiny. Beta(2,2) here was picked as illustratively "a bit more informative than uniform"; in practice, an informative prior needs to be justified from prior data (e.g. rates observed on similar features historically), not chosen to nudge the result.
• With very little data, prior choice dominates and this distinction matters a lot; with $n=100$ here it barely moves the estimate, but the same exercise with $n=10$ would show Beta(2,2) and Beta(1,1) diverging noticeably.
• The Beta-Binomial conjugacy is a special case. It makes this calculation closed-form; most real posteriors (non-conjugate priors, hierarchical models) require MCMC or variational inference to get a credible interval numerically.

When an engine has no native PIVOT operator, conditional aggregation (SUM wrapped in CASE, one CASE per target column) builds the same rows-to-columns transformation manually.

Structured elaboration

SELECT product_id,
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-01' THEN amount ELSE 0 END) AS "2025-01",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-02' THEN amount ELSE 0 END) AS "2025-02",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-03' THEN amount ELSE 0 END) AS "2025-03",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-04' THEN amount ELSE 0 END) AS "2025-04",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-05' THEN amount ELSE 0 END) AS "2025-05",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-06' THEN amount ELSE 0 END) AS "2025-06"
FROM sales
GROUP BY product_id;

Each output column is its own conditional SUM, so this is really the same conditional-aggregation technique from splitting one metric into two channel columns, extended to as many columns as there are target months. The month list has to be known and written out at query-authoring time; there is no way to make the number of output columns dynamic in standard SQL, since the SELECT list's shape is fixed at parse time. Concretely, if today is 2025-06-15 and "last 6 months" means the trailing 6 calendar months ending at the current one, the six columns to write out are 2025-01 through 2025-06, exactly the six shown above; next month, the same query would need to be edited by hand to shift the window forward to 2025-02 through 2025-07, since nothing about which months are covered is computed automatically.

Worked example

Given product 1 with sales of 100 in January, 50 in February, 20 in March, and 40 in April (no sales in May or June): the query returns one row for product 1 with columns 2025-01 = 100, 2025-02 = 50, 2025-03 = 20, 2025-04 = 40, 2025-05 = 0, and 2025-06 = 0, correctly attributing each month's revenue to its own column and showing an explicit 0, not an absent column, for the two months with no sales at all.

Trade-offs and pitfalls

This hardcoded-column approach doesn't scale past a small, known, relatively stable set of categories (a handful of months, a fixed set of regions); for a genuinely dynamic or large category set, the more maintainable options are either building the pivot SQL dynamically outside the database (generating the CASE list programmatically before sending the query) or handling the pivot in the BI/reporting tool layer instead of in SQL at all.

Requirements & inputs:

• Monthly cohorts: new signups/acquisitions per month (cohort size).
• Conversion rates: % converting from trial/free to paid (if applicable).
• Retention / decay curve: cohort survival vector r0..r11 (month 0 = conversion month).
• ARPU per cohort-month (can be constant or time-varying).
• Churn definition (gross vs net), expansion revenue (upsells), refunds.
• Seasonality index per calendar month (12-month factors).
• Planned product/price changes and marketing campaigns with expected impact and timing.

Forecast math (cohort matrix):

1. For each cohort c (acquired in month t0) and forecast month t:
   Paying users = cohort_size[c] * conversion_rate[c] * retention_curve[t - t0]
2. MRR contribution = paying_users * ARPU[c, t - t0] (include expansion as % uplift)
3. Total MRR for month t = sum over all cohorts with t >= t0.

Including seasonality & product changes:

• Apply multiplicative seasonality factor S(month) to new cohort sizes and/or ARPU where appropriate.
• For planned changes, model explicit deltas: e.g., price increase +10% applied to ARPU from effective month; product launch increases conversion by X percentage points for affected cohorts; model adoption ramp (e.g., 3-month sigmoid).
• For retention effects, adjust retention_curve for cohorts exposed to product changes.

Optimistic / base / pessimistic scenarios:

• Define assumptions ranges for key levers: acquisition volume ±%, conversion ±pp, retention rate curve scaling ±%, ARPU ±%.
• Example: Base = historical means; Optimistic = +10% acquisition, +2pp conversion, retention scaled *1.05, ARPU +5%; Pessimistic = -10% acquisition, -2pp conversion, retention *0.95, ARPU -3%.
• Run the cohort model under each scenario and produce fan charts, scenario tables, and key KPIs (ARR, net new MRR, cohort LTV).

Presentation & deliverables:

• Excel/BigQuery-ready cohort template (cohort-by-age matrix), scenario toggle inputs, and visualizations: stacked MRR timeline by cohort, waterfall of MRR drivers, cohort retention curves, fan chart for uncertainty.
• Document assumptions, confidence levels, and sensitivity analysis (tornado chart showing which inputs drive variance).
• Recommendations: prioritize actions that improve highest-impact levers (retention and ARPU) and monitor leading indicators (trial-to-paid conversion, first 30-day retention).