Lyft Data Scientist Interview Preparation Guide - Junior Level (1-2 Years)

Requirements & constraints:

• Maximize revenue and customer lifetime value while respecting local regulations, taxes, and consumer fairness.
• Support N countries, multiple currencies, volatile FX, distinct VAT/GST, and culturally different price sensitivity.
• Rollout must be testable (causal inference), auditable, and reversible.

High-level plan (phases):

1. Data collection & pipelines

• Sources: transactional purchases, impressions, add-to-carts, cancellations, returns, web/app analytics, promotions, competitor prices, macro data (GDP per capita), FX rates, PPP indices (World Bank/OECD), local tax tables, legal rules.
• Pipeline: ingest via streaming (Kafka) + batch ETL (Airflow -> Spark/DB). Central normalized schema: events, products, users, locations, fiscal metadata.

2. Currency conversion & PPP adjustments

• Store raw txn currency + converted baseline using daily FX rates and a PPP-adjusted price: price_ppp = price_local / PPP_index(country). Keep both nominal-local, FX-USD, and PPP-normalized values. Use anchored base currency for comparisons.

3. Local elasticity estimation

• Models:
  • Start with log-log demand regressions per market to get price elasticity: log(q) ~ alpha + beta*log(price) + controls.
  • Use hierarchical/multilevel Bayesian models to pool information across similar markets and products (partial pooling).
  • For selection/endogeneity, use instrumental variables (cost shocks, competitor price changes) or exploit randomized price experiments.
  • For discrete choices, use multinomial logit/ Mixed Logit for assortment effects.
  • Estimate heterogenous elasticities (by cohort, channel) using gradient boosting/uplift trees for feature-rich segmentation.
• Validation: backtest predictions on holdout periods; simulated counterfactual revenue.

4. Legal & tax checks

• Integrate compliance module: for each country, encode required tax treatment (price-display rules: tax-included vs excluded), max price caps, currency controls, consumer protection constraints. Run automated rule engine pre-deployment and manual legal review for flagged markets.

5. Experiment design across markets

• Design:
  • Multi-armed band for adaptive pricing where allowed; otherwise factorial A/B with stratification by country-product-traffic.
  • Prefer cluster-randomized or user-level randomization depending on spillovers. For country-level policy changes, use stepped-wedge rollout to infer effects while limiting exposure.
  • Predefine primary metrics: revenue per visitor, conversion rate, average order value, retention, and consumer complaints. Use sequential testing with alpha spending (e.g., Pocock/O’Brien-Fleming) and Bayesian stopping rules.
  • Sample size/power calculations per market using historical variance and expected effect size (from elasticity models).

6. Localization of price presentation

• Presentation logic: format currency, decimal separators, local rounding conventions, price-ending psychology (e.g., .99), and tax-inclusion labels.
• UX experiments to test trust signals (local payment methods, localized copy).
• Ensure legal-required disclosures (tax/VAT, shipping) shown prominently.

7. Rollout & monitoring plan

• Staged rollout: pilot in low-risk markets -> regional -> global. Use feature flags and canary deployments.
• Monitoring:
  • Real-time metrics: transactions/sec, conversion, revenue, refunds, chargebacks, support tickets, legal complaints.
  • Statistical monitoring: daily cohort-level uplift estimates, control charts, and CUSUM for drift.
  • Anomaly detection: use unsupervised models (autoencoders, Prophet residuals) to detect deviations from expected demand given price and seasonality.
  • Alerting: thresholds and Bayesian posterior probability (>95% harm) trigger rollback. Tie alerts to runbooks with immediate mitigation steps.
  • Post-rollout attribution: difference-in-differences and synthetic control for markets without randomization.

Key components & tech choices:

• Data: Snowflake/BigQuery, Kafka
• Compute: Spark, Python (pandas/statsmodels/pyro or Stan for Bayesian), scikit-learn, XGBoost
• Orchestration: Airflow
• Experimentation: custom engine or Optimizely/Flagship, with logging for reproducibility
• Monitoring: Prometheus/Grafana, BI dashboards (Looker/Tableau), ML anomaly detection pipelines

Trade-offs & risks:

• Granularity vs variance: per-country/product estimates can be noisy; use hierarchical models to reduce variance.
• PPP smoothing may mask local willingness-to-pay patterns; always validate with local experiments.
• Legal/regulatory risk: prioritize manual legal sign-off where automation flags non-routine rules.
• Consumer fairness/PR: avoid abrupt large disparities across neighboring countries; include fairness constraints in optimization.

Success metrics:

• Statistically significant uplift in revenue-per-visitor without degrading conversion or retention; reduced refund/complaint rates; maintain legal compliance.

This plan provides a reproducible pipeline from data to deployment, combines causal inference and ML for elasticity estimation, respects legal constraints, and includes rigorous experiment and monitoring strategies to safely roll out differentiated pricing globally.

Direct answer

Do not treat the metric win and the UX risk as opposing bets. Before building anything, agree with design and product on the primary success metric and on explicit guardrail metrics chosen specifically to catch the kind of harm the primary metric would not see, then validate cheaply with a prototype or a small qualitative test before committing to a live experiment sized to detect both.

Structured elaboration

Agree on what "good" means before anyone builds

The primary metric, say a conversion or engagement number, tells you if the change works on its own terms. Guardrail metrics are chosen specifically because they would catch harm the primary metric is blind to, such as task completion, return usage a week later, or support-ticket volume. Naming guardrails upfront, with agreed thresholds, prevents "we'll know it if we see it" arguments after the fact.

Validate cheaply before going live

A clickable prototype or a small moderated usability session can surface confusion or trust issues that the metric alone cannot catch, at a fraction of the cost of a live experiment. This is not a substitute for the experiment, it is a cheap filter that catches the worst ideas before they reach real users.

Run a bounded experiment, not a full rollout

Start with a small slice of traffic, watch both the primary metric and the guardrails, and decide the stopping rule, meaning what result on which metric ends the test, before the test starts, not after you see the numbers.

Decide and communicate together

If the primary metric improves but a guardrail moves the wrong way, that is a real finding, not a technicality to explain away. Whether to ship, iterate, or drop the idea is a joint call between design, product, and whoever owns the guardrail metric, made against the thresholds agreed upfront.

Worked example

Design proposes reordering a list of recommended items to increase click-through rate. The concern is that users may have learned to expect a stable, predictable order, and reordering it could hurt their ability to quickly find what they are looking for on repeat visits, something click-through rate would not show because a user can click more and still be more frustrated.

Before building, the group agrees the primary metric is click-through rate, and the guardrails are task completion rate (did the user's search end in the outcome they were after) and a return-usage check at one week out. A moderated usability test with a handful of participants on a clickable prototype surfaces that new users find the reordered list fine, but a couple of returning participants mention it "looks different" and take longer to find what they normally click first. That is a signal, not a stop sign: the team ships the change to a small slice of traffic, watches both metrics for an agreed window, and only expands the rollout if task completion holds steady alongside the click-through gain.

Trade-offs and pitfalls

Over-instrumenting every change with a full guardrail suite slows teams down and trains people to skip the process for anything that feels small. Guardrails should be chosen deliberately for the specific risk in question, not applied as a blanket checklist.

The sharpest failure mode is agreeing on guardrails in principle but not on thresholds, so when a guardrail moves slightly, the debate about whether it is a real regression happens after the data is already in and someone has already committed emotionally to shipping. Fixing the threshold before the test removes that fight.

Direct answer

Leading indicators predict a future outcome and are observable early, before the outcome is realized; lagging indicators are the outcome itself, confirmed only after the fact. The same distinction recurs whether the domain is a subscription product, an operational process, or an e-commerce funnel; what changes is which specific metrics play each role.

Structured elaboration

In practice, monitor leading metrics weekly to trigger interventions, and use lagging metrics monthly or quarterly to confirm whether those interventions actually moved the business.

Worked example

Take a cohort of 800 new users and split it by whether they reached first value quickly.

The 30 percentage-point gap between the fast-TTFV group and the slow-TTFV group is why TTFV qualifies as a leading indicator here: it is observable in week one, and it predicts a retention outcome that would not otherwise be confirmed until the month-2 lagging number lands.

Trade-offs & pitfalls

• A leading indicator is only useful once validated against the lagging outcome; an unvalidated candidate might just correlate with pre-existing high-intent users rather than causing them to stick (a self-selection confound, not a causal signal).
• Some candidates are simultaneously too noisy to trust and too close to the lagging outcome to be actionable; check both properties before adopting one.
• Managing exclusively to a leading indicator invites Goodhart's-law-style gaming: pushing users through a shallow "first action" checkbox can inflate the leading metric without the lagging outcome it was meant to predict ever improving.
• A lagging indicator like NRR aggregates upgrades, downgrades, and churn into one number; a flat NRR can hide an improving new-customer retention trend offset by a legacy cohort's decline.

Approach: generate every hourly bucket for the last 30 days, for each zone count (1) drivers whose latest status as of that hour_start is 'idle', (2) requests whose requested_at falls in the hour and that were not matched within 5 minutes (matched_at is null or matched_at > requested_at + interval '5 minutes'), then compute imbalance = unmatched_requests - idle_driver_count.

-- ANSI-ish SQL using a recursive CTE to generate hourly buckets
WITH params AS (
  SELECT
    CAST(current_timestamp AS timestamp)                       AS now_ts,
    CAST(current_timestamp - interval '30 days' AS timestamp)   AS start_ts
),
hours AS (
  SELECT start_ts AS hour_start FROM params
  UNION ALL
  SELECT hour_start + interval '1 hour'
  FROM hours
  JOIN params ON hour_start + interval '1 hour' <= params.now_ts
),
zones AS (
  -- get all zones seen in either table in the window to ensure full coverage
  SELECT DISTINCT zone_id FROM drivers_status
  WHERE updated_at >= (SELECT start_ts FROM params) - interval '1 day'
  UNION
  SELECT DISTINCT zone_id FROM ride_requests
  WHERE requested_at >= (SELECT start_ts FROM params) - interval '1 day'
),
hour_zone AS (
  SELECT z.zone_id, h.hour_start
  FROM zones z
  CROSS JOIN hours h
),
-- For drivers: latest status per driver/zone as of each hour_start
driver_latest AS (
  SELECT
    hz.zone_id,
    hz.hour_start,
    ds.driver_id,
    ds.status,
    ds.updated_at,
    ROW_NUMBER() OVER (PARTITION BY hz.zone_id, hz.hour_start, ds.driver_id
                       ORDER BY ds.updated_at DESC) AS rn
  FROM hour_zone hz
  JOIN drivers_status ds
    ON ds.zone_id = hz.zone_id
   AND ds.updated_at <= hz.hour_start + interval '59 minutes 59 seconds'  -- as of that hour
)
,
idle_counts AS (
  SELECT zone_id, hour_start, COUNT(*) AS idle_driver_count
  FROM driver_latest
  WHERE rn = 1 AND status = 'idle'
  GROUP BY zone_id, hour_start
),
-- For requests: those requested in the hour and unmatched within 5 minutes
unmatched AS (
  SELECT
    rq.zone_id,
    date_trunc('hour', rq.requested_at) + (extract(hour from rq.requested_at)::int % 24) * interval '1 hour' AS hour_start, -- ensure align
    COUNT(*) AS unmatched_requests
  FROM ride_requests rq
  WHERE rq.requested_at >= (SELECT start_ts FROM params)
    AND rq.requested_at <= (SELECT now_ts FROM params)
    AND (rq.matched_at IS NULL OR rq.matched_at > rq.requested_at + interval '5 minutes')
  GROUP BY rq.zone_id, date_trunc('hour', rq.requested_at)
)
SELECT
  hz.zone_id,
  hz.hour_start,
  COALESCE(ic.idle_driver_count, 0)      AS idle_driver_count,
  COALESCE(u.unmatched_requests, 0)      AS unmatched_requests,
  COALESCE(u.unmatched_requests, 0) - COALESCE(ic.idle_driver_count, 0) AS imbalance
FROM hour_zone hz
LEFT JOIN idle_counts ic
  ON ic.zone_id = hz.zone_id AND ic.hour_start = hz.hour_start
LEFT JOIN unmatched u
  ON u.zone_id = hz.zone_id AND u.hour_start = hz.hour_start
ORDER BY hz.zone_id, hz.hour_start;

Key points:

• driver_latest finds each driver's most recent status at or before the hour (ROW_NUMBER by updated_at DESC).
• A request is "unmatched within 5 minutes" if matched_at is null or occurs more than 5 minutes after requested_at.
• We generate all zone×hour combos to report zeros where applicable.
  Performance notes / alternatives:
• For large tables, pre-aggregate drivers' last status per driver per relevant cutoff timestamps or use a lateral/partition-ordered approach (e.g., LAST_VALUE with RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) in DBs that support it.
• Consider materializing hourly driver states or using event-time tables for scalability. Edge cases: drivers changing zones between updates (we use the zone in drivers_status at updated_at), clock skew, requests exactly matched at 5 minutes (treated as matched within 5 min).

Direct answer

I would implement a relative loss-change criterion combined with patience (stop only after the criterion holds for several consecutive checks), rather than a bare absolute threshold, because absolute thresholds don't transfer across problems with different loss scales and are easily fooled by a single noisy step in SGD. A gradient-norm criterion is the theoretically cleanest signal but is expensive to compute exactly and needs its own smoothing under stochastic gradients.

Structured elaboration

Absolute loss change:

$|L_t - L_{t-1}| < \epsilon_{\text{abs}}$

Simple, but $\epsilon_{\text{abs}}$ must be hand-tuned to the loss's scale, a threshold that's sensible for a loss around 1.0 is meaningless for a loss around 1000.

Relative loss change:

$\frac{|L_t - L_{t-1}|}{\max(1, |L_{t-1}|)} < \epsilon_{\text{rel}}$

Scale-invariant, which is why it's usually preferable to a raw absolute threshold, but still vulnerable to a single lucky (small) step in a noisy loss trace triggering an early stop.

Gradient norm:

$\|\nabla L(\theta_t)\|_2 < \epsilon_{\text{grad}}$

The most theoretically direct measure of "near a stationary point," but computing a full gradient can be expensive at scale, and a stochastic mini-batch gradient's norm is itself noisy, so this usually needs averaging over several steps rather than a single check.

Practical combination: apply the relative-change (or gradient-norm) criterion to a smoothed loss (moving average over the last k steps, not the raw per-step value), and require the criterion to hold for several consecutive checks (patience) before stopping, this filters out the single-step noise that would fool a naive threshold.

Worked example

Consider a hypothetical noisy loss trace over 10 epochs (illustrative, computed here purely to show the failure mode, not from an actual training run): 2.50, 2.10, 2.30, 1.90, 1.92, 1.70, 1.85, 1.60, 1.62, 1.55.

At epoch 4 to 5, the change is only 0.02 (1.05% relative), small enough that a naive absolute threshold of $\epsilon_{\text{abs}} = 0.05$ would trigger a stop right there. But the loss is still 1.92 at that point and keeps improving all the way down to 1.55 by epoch 10, that single small gap at epoch 4 to 5 was noise, not convergence. A patience of, say, 3 consecutive small-change epochs would have correctly kept training past epoch 5, since epoch 5 to 6 jumps back up to a 0.22 change.

Trade-offs & pitfalls

• Absolute thresholds are the easiest to get wrong, they silently assume you know the loss's natural scale in advance; prefer relative change unless you have a strong reason not to.
• Gradient-norm checks under SGD need smoothing (e.g., exponential moving average of the norm), a single mini-batch's gradient norm can be small purely by chance even far from convergence.
• Optimization convergence and generalization are different questions. A training-loss convergence criterion tells you the optimizer has (approximately) stopped improving on the training objective, it says nothing about overfitting, pair it with validation-based early stopping if generalization is the actual goal.
• Pitfall: using a single-step check with no patience under any noisy training signal (SGD, RL, or otherwise). One lucky (or unlucky) step is not evidence of convergence, always require the criterion to hold consistently over a short window before acting on it.

High-level pipeline for a small team:

1. Data ingestion: stream logs to a message bus (Kafka) and store raw events in object storage (S3) with partitioning by date.
2. ETL / Feature engineering: nightly Spark job to parse logs, join user/context tables, compute aggregates, and produce a feature parquet dataset; store feature specs and lineage.
3. Training: use a scheduled job (Airflow) to pull latest features, train a binary classifier (e.g., XGBoost) with cross-validation, log metrics and artifacts to model registry (MLflow).
4. Validation: run holdout evaluation, fairness and calibration checks, and unit tests. Require approval if metrics worsen.
5. Deployment: package model as a REST/gRPC microservice behind feature adapter that computes features online (real-time store + lookups). Use canary rollout and health checks.
6. Monitoring: collect prediction distributions, feature drift, data quality alerts, and business KPIs; log inputs and outputs for debugging.
7. Retraining triggers: automated retrain when (a) data drift exceeds threshold, (b) model performance degrades on recent holdout, or (c) periodic schedule (weekly/monthly). Use CI for retrain with automatic evaluation gating and human review for production promotion.

Direct answer
Restate the instruction in your own words as a quick confirmation before acting, framed as checking your own understanding rather than doubting them, so it reads as diligence rather than not having listened.

Structured elaboration

• Frame it as confirming your own plan, not re-asking their request. "Just to make sure I act on the right thing, my plan is to do X, does that match what you meant?" reads very differently from "wait, what did you want again?"
• Be specific in the paraphrase, not generic. A vague paraphrase ("okay, got it, I'll handle it") gives them nothing to correct if you actually misunderstood; a specific one gives them an easy, fast way to say "actually, no" if needed.
• Do it briefly and move on. One sentence of confirmation, not a lengthy negotiation over wording; the goal is a fast check, not a renegotiation of the request.
• If genuinely rushed, confirm asynchronously right after rather than not at all: a one-line follow-up message restating what you understood, sent immediately after the quick instruction, still catches a misunderstanding before you've acted on it.

Worked example
Instruction given quickly in passing: "Can you get that report over to finance today?"

Weak version: "Yep, will do." (No confirmation of which report, which finance contact, or what today means if it's late in the day.)

Better version: "On it, I'll send the Q3 variance report to Priya in finance by end of day, that's the one you mean?"

This surfaces, in one sentence, exactly which report, which recipient, and what "today" means, giving them a fast chance to correct any of the three if you guessed wrong, without making them repeat the whole instruction.

Trade-offs and pitfalls

• Doing this for every trivial instruction can come across as needing excessive hand-holding; reserve the explicit paraphrase for instructions with real ambiguity or real consequences if you get it wrong.
• A paraphrase that's too close to a verbatim repeat of their words doesn't actually test whether you understood the intent, only whether you can repeat words back; try to restate it in language that shows you grasped the underlying goal, not just the surface phrasing.
• If they seem rushed or impatient with the confirmation, a very short version ("Q3 report to Priya today, correct?") gets the same benefit with almost no added time.

Direct answer
Detecting a structural break means testing whether a series' underlying parameters (mean, trend, variance) shifted at some point, using methods like the Chow test (tests a hypothesized break date), CUSUM (accumulates deviations to flag a sustained shift), or Bayesian change point detection (estimates a posterior distribution over where a break most likely occurred); attributing the break to a specific cause (like a pricing change) versus a coincident event requires comparing the break's timing and shape against what you'd expect that specific cause to produce, ideally with a control series that wasn't exposed to the same cause.

Structured elaboration

• Chow test: tests a SPECIFIC, pre-specified candidate break date by comparing the fit of one regression over the whole series against two separate regressions split at that date; an F-test determines whether the split significantly improves fit. Use it when you already have a hypothesis for WHEN the break happened (e.g. the exact date of a pricing change).
• CUSUM: accumulates signed deviations from a reference/target value over time and flags when the cumulative sum crosses a threshold; naturally suited to detecting a SUSTAINED shift (as opposed to a single outlier), and doesn't require knowing the break date in advance.
• Bayesian change point detection: puts a prior over possible break locations (and possibly the number of breaks) and returns a posterior distribution, which is useful when you're not confident there's exactly one break, or want an honest measure of uncertainty about WHERE the break occurred rather than a single point estimate.
• Attributing the break to pricing vs a coincident event: check whether the break's TIMING lines up precisely with the pricing change date (a break that starts 3 weeks before the price change is a red flag that something else is driving it); check whether the break's DIRECTION and MAGNITUDE make sense for the mechanism (a price increase should plausibly reduce volume, not increase it, absent some other explanation); and, most powerfully, compare against a CONTROL series not exposed to the pricing change (a comparable product/region where price didn't change) - if the control also shows a break at the same time, the true cause is more likely something coincident (a seasonal event, a macro shift) than the pricing change itself.

Worked example (executed)
Run against a synthetic 300-point series (seed=0) with an abrupt level shift of +5 at index 100, followed by a separate GRADUAL linear ramp of +5 starting at index 200 and completing by index 300, both with unit-variance Gaussian noise:

import numpy as np, ruptures as rpt
np.random.seed(0)
n = 300
x = np.zeros(n)
x[:100] = np.random.normal(0, 1, 100)
x[100:200] = np.random.normal(5, 1, 100)
ramp = np.linspace(0, 5, 100)
x[200:300] = 5 + ramp + np.random.normal(0, 1, 100)
algo = rpt.Pelt(model="rbf").fit(x.reshape(-1, 1))
result = algo.predict(pen=10)

Executed output: result = [100, 235, 300]. PELT located the abrupt shift's true onset (index 100) EXACTLY, but only flagged the gradual ramp at index 235 - thirty-five periods after its true onset at index 200 - a genuine, informative result: algorithms tuned for abrupt shifts (Chow test at a hypothesized date, standard CUSUM/PELT) can systematically under-detect or mis-locate a GRADUAL regime change, since there's no single sharp point where the "before" and "after" distributions are maximally separated. If a pricing change caused a gradual behavioral adaptation rather than an immediate jump, methods built around detecting sharp breaks will underperform, and a trend-based or rolling-regression approach may localize the shift better.

Trade-offs & pitfalls
At the scale of many series at once (e.g. 10 million SKU-store combinations), running full changepoint detection on every series individually is computationally prohibitive; a practical architecture screens cheaply first (a simple rolling-mean-shift heuristic) and reserves the expensive Bayesian/PELT-style methods for the subset flagged as plausible candidates, with human review prioritized toward breaks that are both large in magnitude AND business-critical, rather than trying to review every detected break. After a deployment or pricing change specifically, build the investigation triage into a repeatable runbook (a rollback threshold, a standard set of first checks) rather than an ad hoc analysis each time, since "did our recent change cause this" is a recurring question, not a one-off.

Direct answer

Monthly churn for month M compares each user's activity in month M-1 (the reference month) against month M (the evaluation month): a user counts as churned if they had at least one event in M-1 and zero events in M. The edge case that needs an explicit decision is exclusion: a user is only eligible to churn out of a month if they already existed as a user before the evaluation month began.

Structured elaboration

• Build a de-duplicated user-month activity table first (one row per user per month with any activity). Every other step is a self-join against this table, shifted by one month.
• "Active in M-1" defines the churn base, the denominator. "Inactive in M" defines who churned, the numerator.
• Edge case decided: a user who signs up mid-month and has one event that same month is treated as "active" for that month like anyone else. No partial-month handling.
• Edge case decided: users whose signup falls inside the evaluation month M itself are excluded from the base. In practice this guards mainly against stray pre-signup test events, since a genuinely new user has no prior-month activity to begin with, but it is worth naming explicitly rather than leaving implicit.
• Sparse months (zero users active in M-1) return a NULL rate rather than raising a divide-by-zero error. This only holds if the denominator (eligible_prev_active) is computed by joining the generated month spine to the eligible-user rows with a LEFT JOIN, as shown below. Grouping the eligible-user rows directly (an inner join under the hood) silently drops any month whose base is empty instead of returning a zero-row.

Worked example

Sample data, tested in DuckDB 1.5:

users:

events:

WITH months AS (
  SELECT UNNEST(generate_series(
    date_trunc('month', (SELECT MIN(event_time) FROM events))::date,
    date_trunc('month', (SELECT MAX(event_time) FROM events))::date,
    INTERVAL 1 MONTH
  )) AS month_start
),
user_month_activity AS (
  SELECT user_id, date_trunc('month', event_time)::date AS month_start
  FROM events
  GROUP BY 1, 2
),
prev_month_active AS (
  SELECT m.month_start, ua.user_id
  FROM months m
  JOIN user_month_activity ua ON ua.month_start = (m.month_start - INTERVAL 1 MONTH)::date
),
curr_month_active AS (
  SELECT m.month_start, ua.user_id
  FROM months m
  JOIN user_month_activity ua ON ua.month_start = m.month_start
),
eligible_prev AS (
  SELECT p.month_start, p.user_id
  FROM prev_month_active p
  JOIN users u ON u.user_id = p.user_id
  WHERE NOT (u.signup_date >= p.month_start AND u.signup_date < (p.month_start + INTERVAL 1 MONTH))
),
monthly_churn AS (
  SELECT e.month_start, COUNT(*) AS churned_users
  FROM eligible_prev e
  LEFT JOIN curr_month_active c ON e.user_id = c.user_id AND e.month_start = c.month_start
  WHERE c.user_id IS NULL
  GROUP BY 1
),
prev_counts AS (
  SELECT m.month_start, COUNT(e.user_id) AS eligible_prev_active
  FROM months m
  LEFT JOIN eligible_prev e ON e.month_start = m.month_start
  GROUP BY 1
)
SELECT
  pc.month_start,
  pc.eligible_prev_active,
  COALESCE(mc.churned_users, 0) AS churned_users,
  CASE WHEN pc.eligible_prev_active = 0 THEN NULL
       ELSE ROUND(100.0 * COALESCE(mc.churned_users,0) / pc.eligible_prev_active, 2)
  END AS churn_pct
FROM prev_counts pc
LEFT JOIN monthly_churn mc USING (month_start)
ORDER BY pc.month_start;

Result:

Trace: November's reference month (October 2023) has no data at all, so the base is 0 and the row correctly surfaces with churn_pct = NULL instead of being dropped. December's base is just user 4 (active in November), who stayed active in December, so 0 churned. January's base is users 1, 2, 4 (active in December); user 4 has no January event, so 1 churned (33.33%). February's base is users 1, 2, 3 (active in January); users 2 and 3 have no February event, so 2 churned (66.67%). User 3, who signed up January 10 and is active in January, is correctly included in February's base (not excluded), since the exclusion rule only protects against signing up during the evaluation month itself, not the reference month.

Trade-offs & pitfalls

• This needs one row per user per active month, not per event, so pre-aggregating activity into a monthly rollup table is worth it once the raw events table is large. Recomputing MIN/MAX over the whole events table for every month-spine generation, as shown here, does not scale, precompute the bounds instead in production.
• The "excluded if new this month" rule only bites on unusual data (pre-signup test events). Most of its practical effect is defensive. It is easy to mistake it for something bigger, like a grace period, so state its actual scope explicitly to stakeholders.
• generate_series / date_trunc / INTERVAL syntax shown is DuckDB/PostgreSQL. MySQL and SQL Server need a numbers-table or recursive CTE substitute for generating the month spine.
• Reactivation (a churned user returning two months later) is not double-penalized or double-rewarded, each month's churn is evaluated independently against only the immediately preceding month.
• Building the denominator (prev_counts) by grouping straight off an inner-joined CTE looks equivalent to grouping off the month spine with a LEFT JOIN, but it is not: any month whose base is genuinely empty vanishes from the result set instead of appearing with a NULL rate. Always drive the final row set from the spine, not from the innermost joined table, when a zero-count month needs to be visible.

Direct answer
Under the standard regression model $y = f(x) + \epsilon$ with $E[\epsilon]=0$, the expected squared prediction error of an estimator $\hat f$ splits cleanly into three non-negative terms: irreducible noise variance, squared bias, and variance of the estimator. None of the three can be eliminated by choosing a better algorithm alone (noise is a property of the data), but bias and variance trade off against each other as you change model complexity.

Structured elaboration
Start from the definition and expand:
$E[(y-\hat f(x))^2] = E[(f(x)+\epsilon-\hat f(x))^2]$

Expand the square. Cross terms involving $\epsilon$ vanish in expectation because $E[\epsilon]=0$ and $\epsilon$ is independent of $\hat f(x)$ (which depends only on the training data, not the test-point noise):