Netflix Data Analyst Interview Preparation Guide – Junior Level

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.

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. Partition pruning lets the planner skip reading entire partitions whose range can't possibly satisfy the query's predicate, based purely on partition boundaries, without opening those partitions at all; it silently fails whenever the predicate doesn't let the planner make that boundary comparison directly, most commonly because the partition column is wrapped in a function or absent from the predicate entirely.

Structured elaboration. For a table partitioned by a date or key range, each partition covers a known, non-overlapping slice of that column's values. If a query's WHERE clause directly compares the partition column to a constant or a bound range, the planner can compare that predicate against each partition's known boundaries at planning time and discard any partition that can't possibly contain a match, before ever touching its data. This fails in three common shapes: wrapping the partition column in a function (DATE(created_at) = ... instead of a bare range on created_at) hides the column's raw value from the planner's boundary comparison; a predicate that never references the partition column at all gives the planner nothing to prune against; and a join whose predicate doesn't align with partition boundaries can force scanning every partition on one or both sides even if each individual table would prune well on its own.

I verified the function-wrapping failure mode concretely: over a table with events spread across 60 days, a predicate wrapping the date column in a string cast (CAST(event_date AS VARCHAR) LIKE '2025-01%') and the equivalent sargable range predicate (event_date >= DATE '2025-01-01' AND event_date < DATE '2025-02-01') both correctly return the same 5,176 matching rows, confirming the rewrite doesn't change the RESULT; what changes is that only the sargable, unwrapped form gives a partitioned engine's planner the direct boundary comparison it needs to prune non-matching partitions before scanning them, while the wrapped form forces it to at least consider every partition.

-- defeats pruning: the partition column is hidden inside a function
SELECT count(*) FROM events WHERE CAST(event_date AS VARCHAR) LIKE '2025-01%';

-- enables pruning: a direct range comparison on the bare partition column
SELECT count(*) FROM events WHERE event_date >= DATE '2025-01-01' AND event_date < DATE '2025-02-01';

Worked example. For a query joining a partitioned events fact table to a users dimension table, filtered by user_id but not by the partition date at all, every partition of events has to be scanned regardless of how few users actually match, since nothing in the predicate lets the planner rule any date range out; adding an explicit, even broad, date bound to the query (if the business logic allows one) restores pruning even though the "real" filter is on user_id.

Trade-offs and pitfalls. Confirming pruning actually happened, rather than assuming it did because the predicate LOOKS sargable, requires checking the plan itself for how many partitions were actually scanned versus how many exist; a subtly wrong predicate shape can silently force a full scan of every partition while still returning the correct result, which makes this a performance-only bug that's easy to miss without explicitly checking.

Complexity

Effective pruning turns an O(all partitions) scan into O(matching partitions), which for a well-partitioned time-series table can be a difference of months or years of data touched versus a single day or week.

Edge cases

A predicate that spans a partition boundary (a date range crossing from one partition into the next) still requires scanning every partition the range touches, which is correct and expected, not a pruning failure; the concern is specifically about partitions the predicate could never match at all still getting scanned.

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
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.

Clean first, then flag: trim and lowercase before applying a pattern check, since a lot of "invalid" emails are actually valid values wrapped in whitespace or inconsistent casing.

Approach

```sql
SELECT id,
TRIM(LOWER(raw_email)) AS cleaned_email,
TRIM(LOWER(raw_email)) NOT LIKE '%_@%.%' AS still_invalid
FROM contacts;
```
A lightweight `LIKE`-based pattern is intentionally permissive; it catches obviously broken values (missing '@', no domain) without rejecting valid-but-unusual real addresses that a strict RFC-compliant regex would incorrectly flag.

Worked example

" John@Example.com" cleans to "john@example.com" and passes; "not-an-email" and "user@" both fail even after cleaning, correctly surfacing as genuinely malformed.

Trade-offs and pitfalls

The trade-off between a lightweight rule and a strict regex is really a trade-off between false negatives (letting some genuinely bad addresses through) and false positives (rejecting real, unusual addresses); for most analytics use cases, a permissive rule paired with a downstream domain-level aggregation (grouping malformed rows by which email domain they came from) is more useful than a stricter rule, because it tells you WHERE the bad data is coming from, which is usually more actionable than a binary pass/fail count. Whether to store the cleaned value in the warehouse or clean it at query time is itself a choice: storing it is faster to query repeatedly but means every future value must go through the same cleaning step consistently, or the stored and live-computed values will silently diverge.

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.

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.

Approach
I would model the customer segment as a slowly changing dimension, meaning a history table with valid_from and valid_to timestamps. Then I would do an as-of join, which matches each order to the dimension row that was valid when the order happened, not the current row.

SELECT
  DATE_TRUNC('month', o.order_ts) AS month,
  d.segment,
  SUM(o.revenue) AS revenue
FROM orders o
JOIN dim_customer_history d
  ON o.customer_id = d.customer_id
 AND o.order_ts >= d.valid_from
 AND o.order_ts <  d.valid_to
GROUP BY 1, 2;

Why this is auditable

• The join is driven by the order timestamp, so reclassifying a customer today does not rewrite history.
• I would keep immutable raw orders, the dimension history, and the monthly mart as separate layers.
• I would store the load batch or snapshot date so finance can reproduce the exact closing report.

Late data handling
If an order is backfilled after month close, I would rerun the affected month from raw facts, but only against the dimension history that was effective at the order time. That keeps the numbers consistent and explainable during reconciliation.

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.