Netflix Senior Data Analyst Interview Preparation Guide

Situation: In a previous role, a recurring report was built by copying data into several sheets, manually fixing headers, and retyping formulas each week. It was slow, and small copy-paste mistakes caused mismatches in the final numbers.

Task: I was asked to make the process faster, easier to use, and more trustworthy for the wider team.

Action:

• I mapped the full workflow and identified where errors were happening.
• I turned the raw input into a structured Excel Table and moved the transformation logic into reusable formulas and a refreshable summary sheet.
• I added simple validation checks for blanks, duplicate keys, and out-of-range values.
• I documented the steps so someone else could refresh the report without knowing the original build logic.
• I ran the old and new versions in parallel for a couple of cycles and reconciled the outputs line by line.

Result: The new process removed most of the manual handling, reduced rework, and made the report easier for others to maintain. Just as important, the team gained confidence because the checks made issues visible before distribution.

Learning: I learned that a good reporting fix is not just automation; it is automation plus validation and documentation.

To implement LinUCB with ridge-regularized linear reward estimates, maintain per-arm A = D x D matrix (Gram + λI) and b = D vector (design*reward). At selection time compute theta = A^{-1} b and upper-confidence bound: x^T theta + α * sqrt(x^T A^{-1} x). After observing reward r, update A += x x^T and b += r x.

import numpy as np

class LinUCB:
    def __init__(self, n_arms, dim, alpha=1.0, ridge=1.0):
        self.n = n_arms
        self.d = dim
        self.alpha = alpha
        self.ridge = ridge
        # Per-arm matrices/vectors
        self.A = np.array([np.eye(dim) * ridge for _ in range(n_arms)])  # shape (n, d, d)
        self.b = np.zeros((n_arms, dim))                                 # shape (n, d)

    def select(self, x):
        # x: (d,) context vector (assumed normalized if needed)
        p = np.empty(self.n)
        for a in range(self.n):
            A_inv = np.linalg.inv(self.A[a])          # or use solve/cholesky for stability
            theta = A_inv.dot(self.b[a])
            s = x.dot(A_inv).dot(x)                   # variance term
            p[a] = x.dot(theta) + self.alpha * np.sqrt(s)
        return int(np.argmax(p))

    def update(self, arm, x, reward):
        # x: (d,) reward: scalar
        x = x.reshape(-1)
        self.A[arm] += np.outer(x, x)
        self.b[arm] += reward * x

Key points:

• Use Cholesky updates or Sherman-Morrison to avoid full inverse each time (numerically stable & faster).
• Regularization (ridge) ensures A is invertible.

Computational cost per update:

• Naïve: forming outer product O(d^2) and adding to A, plus in selection computing inverse per arm O(n * d^3) if recomputed each time.
• Improved: maintain A^{-1} with Sherman-Morrison rank-1 update — selection cost O(n * d^2) (matrix-vector products), update cost O(d^2) for rank-1 inverse update.
  Edge cases:
• High-dimensional contexts (use dimensionality reduction)
• Very small ridge -> numerical instability
• Non-stationary rewards (use sliding window or decay)

Direct answer
I treat a company's published culture and values as a claim to be tested, not a fact to accept, and I look for evidence in three places: how people describe real, specific incidents (not slogans) when I ask about them, whether the org's actual structures and incentives would make the stated behavior easy or hard to practice, and whether the story is consistent across different people I talk to in the process.

Structured elaboration

1. Ask for a specific recent incident, not a description of the value. A question like "tell me about a time the team had to choose between shipping fast and following the documented review process" forces a real story; a question like "how would you describe the engineering culture here" invites a rehearsed, values-page-adjacent answer that tells you little.
2. Check whether the org's structure actually supports the stated value, independent of what anyone says. If a company claims to value psychological safety but every interviewer you meet is visibly guarded about naming any team problem, or if a company claims strong autonomy but every technical decision in the loop turns out to require a director's sign-off, the structural evidence contradicts the claim regardless of the wording used to describe it.
3. Triangulate across multiple people, ideally at different levels and tenures. A single enthusiastic interviewer proves little; a hiring manager, a peer-level engineer, and someone from a different function independently describing the same specific behavior (not the same slogan) is much stronger evidence.
4. Ask what the company would do differently if it stopped believing the value, and watch for a concrete, structural answer versus a vague one. People who work inside a genuinely lived value can usually name a real trade-off it costs them; people describing marketing usually cannot.
5. Treat your own discomfort as data. If a described norm (pace, feedback directness, decision-making style) makes you visibly uneasy during the process itself, that is a more reliable signal about fit than anything printed on the careers page, because it is your own live reaction rather than a claim you are being asked to evaluate secondhand.

Worked example
Suppose a company's careers page says it "empowers engineers with high autonomy." During the loop, ask the hiring manager for a specific recent example: "Tell me about the last time an engineer on this team made a production architecture decision without it going through a review committee first." A genuine, lived-autonomy answer sounds like: "Last quarter one of our engineers decided independently to switch a service from synchronous to async processing after noticing latency complaints; she looped in two people for a sanity check, shipped it, and reported the outcome in the next team sync." A marketing-only answer sounds like: "We really believe in empowering our engineers," repeated with no specific incident when pressed twice. If a peer engineer you speak to separately can also describe a comparable specific incident in their own words, that consistency is strong corroborating evidence; if the hiring manager's story turns out to be the ONLY example anyone can produce company-wide, that is itself informative about how common the behavior actually is.

Trade-offs & pitfalls
The main failure mode is accepting an interviewer's fluent, confident description of the culture as sufficient evidence on its own; confidence and specificity are not the same thing, and a well-rehearsed answer to a values-page question is exactly what a company under-delivering on its stated culture is most likely to have prepared. A second pitfall is over-weighting a single glowing anecdote from one enthusiastic interviewer without checking whether it generalizes; one great story is an anecdote, not a pattern. A third is treating any inconsistency you find as automatically disqualifying: it is normal for a large or growing organization to have real variance across teams, so the useful conclusion is usually about the SPECIFIC team and manager you'd actually join, not the company as a monolithic whole.

Direct answer. A LATERAL join (or CROSS APPLY) lets the subquery on its right-hand side reference columns from the row currently being processed on the left, which a plain JOIN's subquery cannot do; that's exactly what you need to pull "the most recent N related rows per parent row" without a separate window-function pass.

Structured elaboration. An ordinary JOIN's right side is evaluated once, independently of the left row; it has no way to say "give me only the top 3 shipments for THIS SPECIFIC order," because it can't see which order it's currently being joined to while it's being planned. LATERAL removes that restriction: the subquery on the right can reference the current left row's columns directly, so you can write "for this customer, give me their single most recent invoice" as an ordinary correlated subquery shape, but one that's still allowed to return multiple rows and multiple columns like a real join.

Worked example. customers(customer_id): 1, 2. invoices(invoice_id, customer_id, invoice_date): (1, 1, '2025-01-01'), (2, 1, '2025-02-01'), (3, 2, '2025-01-15').

SELECT c.customer_id, i.invoice_id, i.invoice_date
FROM customers c,
LATERAL (
  SELECT invoice_id, invoice_date
  FROM invoices
  WHERE customer_id = c.customer_id
  ORDER BY invoice_date DESC
  LIMIT 1
) i
ORDER BY c.customer_id;

Result: (1, 2, '2025-02-01'), (2, 3, '2025-01-15'). Each customer correctly gets only their single most recent invoice, with the LIMIT 1 applying independently, per customer, inside the LATERAL subquery.

Trade-offs and pitfalls. A window-function alternative (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY invoice_date DESC), then filter to rn = 1) computes the SAME answer, but does so by first ranking ALL invoices across ALL customers in one pass and then filtering, whereas LATERAL with a LIMIT conceptually short-circuits per customer. Which one performs better in practice depends heavily on the engine, the index available on (customer_id, invoice_date), and whether you need just the top 1 versus top N with additional per-row logic; LATERAL tends to shine when the per-parent-row logic is genuinely complex (not just a simple rank-and-filter) or when only a small top-N is needed and a good index lets each LATERAL subquery execution be cheap. For a straightforward single flat top-N-per-group with no extra logic, the window-function form is usually the more idiomatic first choice, and LATERAL is the escape hatch once the per-row logic gets more elaborate than ROW_NUMBER can express cleanly.

Direct answer

Spend the first minutes ruling out a measurement artifact before hunting for a business cause, then work outward through segments, funnel steps, and dependencies in fixed time boxes, and decide up front what dollar-per-hour loss or error rate justifies paging engineering versus opening a product ticket. A structured four-hour process beats an ad hoc one because it forces you to check cheap, high-signal things first.

Structured elaboration

A useful frame for the first question to ask is: is this product-driven (a real behavior change) or data-driven (an artifact of measurement)? That fork resolves quickly from check 1 below, and it should gate everything that follows, because chasing a UX root cause for a tracking bug (or vice versa) wastes the whole window.

Timeboxing: checks 1 to 2 in the first 15 minutes, checks 3 to 5 in the next 45, checks 6 to 8 over the following 90 minutes, leaving the final stretch of the four hours for forming 2 to 3 prioritized hypotheses, running quick validations (synthetic transactions, support-ticket spot checks), and writing the escalation decision.

Escalation decision criteria: escalate to engineering when a specific, user-impacting technical failure is confirmed (an error rate crossing an absolute threshold, a rollbackable deploy implicated); escalate to product when the cause is a pricing, promo, or experiment misconfiguration rather than a technical fault. Either way, post an initial incident note with impact, next step, owner, and next-update time before the four hours are up.

Worked example

Baseline: 10,000 sessions per day convert at 4.0%, or 400 orders. A 5% relative drop brings the rate to 3.8%:

$4.0\% \times (1 - 0.05) = 3.8\%$

On the same session volume, that is 380 orders, a shortfall of 20 orders per day:

$10{,}000 \times (0.040 - 0.038) = 20 \text{ orders}$

At an average order value of $50, the shortfall is worth:

$20 \times \$50 = \$1{,}000 \text{ per day} \approx \$42 \text{ per hour averaged flat}$

But checkout traffic is rarely flat. If half of daily orders occur in a 4-hour peak window, the peak-hour shortfall is:

$\frac{20 \times 0.5}{4} \times \$50 = 2.5 \times \$50 = \$125 \text{ per hour during peak}$

Against a threshold of, say, $500 per hour for an immediate engineering page, this specific 5% drop justifies the structured four-hour process rather than an instant all-hands page, but the same math run against a 40% drop instead of 5% would clear that threshold in the first 15 minutes, which is why the dollar-per-hour calculation, not the percentage alone, should drive the escalation call.

Trade-offs & pitfalls

The most common wrong turn is jumping straight to the payment gateway or a recent deploy because it is the most dramatic-sounding explanation, before confirming the drop is real and not a sampling or pipeline artifact. A second pitfall is treating every segment split as equally important: a cohort split that looks flat in aggregate can still hide a real, revenue-significant drop in the high-value segment, so segmentation should be prioritized by revenue exposure, not just by traffic volume. A third is setting the escalation threshold on percentage drop alone rather than dollar impact, which either escalates too often on noisy small-traffic days or too rarely on a small percentage drop that happens to hit a high-value cohort hard.

Direct answer

Customer acquisition cost (CAC) is what it costs to win a paying customer; lifetime value (LTV) is the profit that customer is expected to generate over their time as a customer; payback period is how long it takes the customer's ongoing profit to repay that acquisition cost; and contribution margin (revenue minus the variable costs to serve that customer) is the multiplier that connects revenue to the profit LTV and payback are actually built from. A company will happily accept a slow payback on a channel that produces low-churn, high-margin customers, and will reject the same payback period on a channel that produces high-churn customers, because payback alone says nothing about what happens after the cash is recovered.

Structured elaboration

Definitions

The naive formula and why it needs a refinement

The simplest version of LTV assumes churn is constant every month, so the expected number of months a customer stays is one divided by churn, and:

$LTV = ARPU \times \text{contribution margin} \times \frac{1}{\text{churn}}$

This is a fair first pass, but real cohorts don't churn at a constant rate: churn is usually highest in the first few months and flattens out for customers who survive past an early cliff. The refinement replaces the constant $1/\text{churn}$ term with a retention curve built from actual cohort data (the fraction of a signup cohort still active at month 1, 2, 3, and so on), summed month by month rather than assumed constant. This matters most for young products where the first few months of a real cohort haven't been observed yet, and for comparing two customer segments with different-shaped retention curves even if their month-1 churn looks similar.

Payback period

$\text{Payback (months)} = \frac{CAC}{ARPU \times \text{contribution margin}}$

This is the number of months of contribution-margin profit needed just to break even on the acquisition spend, before any of that customer's value counts as profit.

Why a company accepts a longer payback on one channel but not another

Payback period alone is a cash-timing number, not a profitability number. A channel with a long payback but a high LTV to CAC ratio (say, enterprise sales or a partnership channel that produces sticky, high-margin, low-churn customers) is a good bet because the profit eventually recovered is large relative to what was spent, even if it takes longer to show up. A channel with a short payback but a low LTV to CAC ratio (aggressive paid social that converts price-sensitive, high-churn customers) can recover its cost quickly and still be a worse long-run bet, because there is little profit left after the payback point. The decision rule is: use payback for cash-flow planning, and use the LTV to CAC ratio to judge whether the channel is worth funding at all.

Sizing the marketing budget from the ratio

Because LTV to CAC expresses how much profit one acquired customer is worth relative to what it cost to get them, it is commonly used in reverse to size a budget: pick the ratio a company is willing to accept (a common rule of thumb is not spending unless LTV is at least three times CAC), solve for the maximum CAC that ratio allows, then multiply that maximum CAC by the number of customers targeted to get the budget ceiling for that channel.

Worked example

Two markets with different pricing, using pinned illustrative assumptions:

Market A (higher-priced): ARPU = $20/month, monthly churn = 3%, contribution margin = 70%, CAC = $150.

$LTV_A = 20 \times 0.70 \times \frac{1}{0.03} = \$466.67$
$\text{Payback}_A = \frac{150}{20 \times 0.70} = \frac{150}{14} = 10.71 \text{ months}$
$\frac{LTV_A}{CAC_A} = \frac{466.67}{150} = 3.11$

Market B (lower-priced, but stickier): ARPU = $8/month, monthly churn = 5%, contribution margin = 65%, CAC = $40.

$LTV_B = 8 \times 0.65 \times \frac{1}{0.05} = \$104.00$
$\text{Payback}_B = \frac{40}{8 \times 0.65} = \frac{40}{5.2} = 7.69 \text{ months}$
$\frac{LTV_B}{CAC_B} = \frac{104.00}{40} = 2.60$

Market B recovers its acquisition cost faster in absolute months (7.69 vs 10.71), but Market A has the stronger ratio (3.11 vs 2.60), meaning each dollar spent acquiring in Market A returns more profit over the customer's life even though the cash comes back more slowly. A company optimizing for near-term cash would favor Market B; a company optimizing for long-run profit per acquisition dollar, with the cash runway to wait, would favor Market A. Neither number alone answers where to invest, which is why both have to be computed market by market, using that market's own ARPU, churn, and contribution margin, rather than one blended global figure.

Trade-offs and pitfalls

• Using a single global CAC or LTV number hides exactly the divergence shown above; blended figures can make a healthy market look mediocre and a fragile one look fine.
• The naive one-over-churn formula overstates LTV for products with a steep early drop-off, because it assumes the flat long-run churn rate applied since day one, when the true early cohort churn was higher.
• Contribution margin is easy to compute wrong by leaving in costs that are actually fixed (shared infrastructure, salaried support staff) rather than truly variable per customer, inflating the margin and both LTV and the ratio downstream of it.
• Chasing a 3x LTV to CAC rule of thumb mechanically, without checking whether the channel's customers are the kind the company can actually retain and support at scale, is a common wrong turn: the ratio is a screening heuristic, not a guarantee.

Direct answer

Reporting cadence should be driven by how often the stakeholder actually needs to make a decision or take action based on the information, how quickly the underlying metric or situation changes, and how much operational or reputational risk a delay in awareness carries, not by a default assumption that more frequent updates are always better.

Structured elaboration

1. Decision frequency. A stakeholder who only makes a relevant decision monthly doesn't benefit from weekly updates; the extra frequency is noise relative to when they'd actually act on it.
2. Metric or situation volatility. A fast-moving, high-variance situation (an active incident, a rapidly shifting metric) needs tighter cadence regardless of the stakeholder's decision rhythm, simply because the picture changes meaningfully between updates.
3. Operational risk of delayed awareness. Even a stable, slow-changing situation may need faster updates if a delay in noticing a problem carries real cost (safety, compliance, reputational exposure).
4. Combine these, don't pick just one. A stable metric feeding an infrequent decision, with low risk from delay, genuinely supports a monthly or quarterly cadence; the same metric attached to a fast-changing, high-risk situation deserves much tighter cadence even if the stakeholder's formal decision cycle hasn't changed.

Worked example

A monthly business review for an executive sponsor whose only relevant decision point is the quarterly budget cycle reasonably gets a monthly summary; a metric tied to an active, still-resolving incident affecting the same initiative reasonably gets updates within hours until it stabilizes, even though it's reported to the same stakeholder, because the volatility and risk profile are entirely different in that period.

Trade-offs and pitfalls

Defaulting to high frequency "to be safe" imposes a real cost: it trains stakeholders to skim rather than read carefully, and it consumes your own time producing updates that don't change anyone's actions. Calibrate cadence to genuine need, and be willing to temporarily increase it during a volatile period and step back down once things stabilize.

Direct answer

Use the standard two-proportion sample-size formula for detecting an absolute difference between a baseline retention rate and a target retention rate: $n = \frac{(z_{\alpha/2} + z_{\beta})^2 \left[p_1(1-p_1) + p_2(1-p_2)\right]}{(p_2 - p_1)^2}$ per arm, where $p_1$ is baseline 14-day retention, $p_2 = p_1 + \text{MDE}$ is the target rate, and $z_{\alpha/2}$, $z_{\beta}$ are the standard-normal critical values for your significance level and power. Plugging in the given numbers requires roughly 6,507 users per arm, about 13,014 total.

Structured elaboration

Where the formula comes from. Under the normal approximation to the binomial, the variance of a single proportion $\hat p$ estimated from $n$ users is $p(1-p)/n$. The variance of the DIFFERENCE between two independent proportions is the sum of their variances, $p_1(1-p_1)/n + p_2(1-p_2)/n$. Solving for the $n$ that makes the test have significance level $\alpha$ (two-sided) and power $1-\beta$ against a true difference of $p_2 - p_1$ gives the formula above; it is exact in the large-sample normal approximation, which is comfortable here since $p_1 \approx 0.20$ and $n$ in the thousands keeps $np(1-p)$ well above the usual rule-of-thumb minimum of 5-10.

Plugging in the given numbers. $p_1 = 0.20$, $p_2 = 0.22$ (a 2 percentage-point absolute uplift), $\alpha = 0.05$ two-sided so $z_{\alpha/2} = z_{0.025} \approx 1.96$, power $= 0.80$ so $z_{\beta} = z_{0.20} \approx 0.8416$.

Assumption 1: treatment-effect stability across time. The formula assumes the true 2pp uplift is constant for the whole 14-day measurement window and does not itself validate that assumption; if the treatment's effect on retention is actually front-loaded (a big day-1 to day-3 bump that fades by day 14) or back-loaded (no visible effect until day 10+), the POWER calculation above is still arithmetically correct for detecting a 2pp difference AT day 14 specifically, but the experiment's DURATION and interim-monitoring plan need to account for that shape separately; a novelty-driven early spike that decays can produce a false sense of a working experiment if you peek at day 3 instead of waiting for the pre-registered day-14 read.

Assumption 2: independence between users. The formula assumes each user's retention outcome is an independent Bernoulli draw. Network effects (one user's retention behavior influencing another's, common in social or collaborative products) violate this and effectively reduce the true sample size below the nominal $n$, which is a real risk for any product where users interact with each other.

Assumption 3: one measurement per user, no repeated exposure ambiguity. The formula assumes a clean per-user binary outcome (retained at day 14, yes or no); if users can be re-randomized or re-enter the experiment, the unit of analysis needs to be fixed before this formula applies.

Worked example

The full derivation above IS the worked example (values in the direct answer section were computed with a pinned, stdlib-only inverse-normal solver rather than asserted from memory, described below). To sanity-check the arithmetic direction: doubling the MDE from 2pp to 4pp should cut the required sample size by roughly 4x, since $n$ scales with $1/(p_2-p_1)^2$. At MDE=4pp, $p_2=0.24$: $n = (2.8016)^2 \times (0.16 + 0.24 \times 0.76) / (0.04)^2 = 7.849 \times 0.3424 / 0.0016 \approx 1{,}680$ per arm, about a 3.9x reduction from 6,507, consistent with the inverse-square relationship (not exactly 4x because $p_2(1-p_2)$ also changes slightly as $p_2$ moves).

Trade-offs and pitfalls

• Using relative uplift language when the formula wants absolute. "2 percentage points" (0.20 to 0.22) is very different from "2% relative uplift" (0.20 to 0.204); confusing the two changes $p_2-p_1$ by an order of magnitude and inflates or deflates $n$ correspondingly. Always confirm which one a stakeholder means before computing.
• Ignoring practical significance vs. statistical significance. A well-powered test for a 2pp absolute lift is a real commitment: at $n \approx 6{,}500$/arm this experiment can run for weeks depending on traffic, and a smaller MDE (say 0.5pp) would multiply the required sample size roughly 16x, which may simply not be feasible in the available traffic and timeline; the sample-size calculation should inform what MDE is realistic to chase, not just be solved mechanically for whatever MDE a stakeholder first proposes.
• Skipping a sequential or group-sequential design when the team is going to look at the data before the target n is reached anyway. If interim peeking is inevitable in practice, the fixed-horizon formula above needs to be paired with an alpha-spending correction (e.g., O'Brien-Fleming bounds) or the true false-positive rate will exceed the nominal 5%.

Direct answer. For any newly launched feature, I would examine segments chosen to test specific hypotheses about who the feature helps, hurts, or is neutral for, not an arbitrary list. Six defensible starting segments: new versus returning users, mobile versus desktop, geography (especially regions with different baseline behavior or connectivity), high-value versus low-value users, power users versus casual users, and users acquired through different channels.

Structured elaboration. Each segment earns its place because it maps to a concrete reason the effect could differ:

• New vs. returning users. New users have no prior mental model of the product, so a feature that changes a familiar workflow can help new users (less to unlearn) while confusing returning users (or vice versa).
• Mobile vs. desktop. Different input methods, screen real estate, and often different underlying codebases mean the same feature can be implemented, discovered, or used very differently across platforms.
• Geography. Connectivity, language, payment methods, and cultural expectations vary enough that a feature's effect can be real in one region and non-existent, or actively negative, in another.
• High-value vs. low-value users. A feature aimed at monetization or engagement often has a ceiling effect: users who are already highly engaged have less room to move, so the effect concentrates in the mid-tier.
• Power users vs. casual users. Power users may be annoyed by a change that removes a workflow they had already optimized around, even when the same change genuinely helps casual users.
• Acquisition channel. Users acquired through different channels arrive with different intent and expectations, so a change tuned for one channel's typical user can look flat or negative in aggregate if it helps one channel and hurts another.

Worked example. Suppose the feature is a simplified checkout flow. I would expect the effect to be largest for new users (who benefit most from fewer steps) and smallest, or even negative, for power users on desktop who had memorized the old flow's keyboard shortcuts. If a segment represents a small share of overall traffic, for example a single acquisition channel that is only 4% of sessions, I would flag upfront that any segment-level effect will need a much larger relative lift to reach statistical significance than the overall population does, since the sample size available for that slice is a small fraction of the total, and I would decide in advance whether that segment is important enough to power the experiment for directly or whether it will only ever support a directional, not a confirmatory, read.

Trade-offs and pitfalls. The temptation is to list every dimension the data warehouse happens to have and check all of them for a significant split; that turns segment selection into an uncontrolled multiple-comparisons exercise where you will find "significant" differences by chance. The discipline is to pre-specify a short list of segments tied to a real mechanism for why the effect could differ, treat any other segment split discovered after the fact as hypothesis-generating rather than confirmatory, and size the analysis (or the underlying experiment) with the smallest segment you actually care about in mind, not just the overall population.

Direct answer

LEAD(start_date) OVER (PARTITION BY user_id ORDER BY start_date) pulls each subscription's next start date into the current row without a self-join, so the gap between one subscription ending and the next beginning is just next_start_date - end_date. A NULL from LEAD means there is no next subscription: label those rows deliberately (commonly "churned"), and pick a business threshold for the gap (say, resumed if the gap is at most 30 days, churned otherwise). The exact same LEAD-over-a-partition idea, applied to a purchases table ordered by purchase_date instead of a subscriptions table, computes time-to-next-purchase for a churn or customer lifetime value (LTV) feature; the one thing that changes is how you treat the rows where LEAD is NULL, because "no next purchase yet" is not the same fact as "this user will never purchase again."

Structured elaboration

• LEAD(col) is the mirror of LAG(col): it reaches forward instead of back, using the same PARTITION BY/ORDER BY contract.
• Gap-based labeling: next_start_date - end_date gives the number of days a customer went without an active subscription; a negative gap means the next subscription started before the current one ended (overlap), which needs its own explicit handling rather than silently falling into "resumed."
• The identical query shape applied to purchases(user_id, purchase_date) gives time_to_next_purchase = LEAD(purchase_date) - purchase_date per purchase, which is a standard input feature for churn models and one building block of an LTV estimate.
• Right-censoring: for a user's most recent purchase, LEAD returns NULL because there simply is no later row in the data you have so far, not because the user is confirmed to never buy again. Treating every NULL as "never" biases the feature toward pessimism for exactly your most recently active users, who are the ones you have had the least time to observe. The standard fix is to compute against an explicit snapshot/as-of date: report "days observed since this purchase with no repeat yet" instead of pretending the time-to-next-purchase is either 0 or infinite.

Worked example

WITH nxt AS (
  SELECT user_id, start_date, end_date,
    LEAD(start_date) OVER (PARTITION BY user_id ORDER BY start_date) AS next_start_date
  FROM subscriptions
)
SELECT user_id, start_date, end_date, next_start_date,
  CASE WHEN next_start_date IS NULL THEN NULL ELSE next_start_date - end_date END AS gap_days,
  CASE
    WHEN next_start_date IS NULL THEN 'churned'
    WHEN (next_start_date - end_date) <= 30 THEN 'resumed'
    ELSE 'churned'
  END AS status
FROM nxt ORDER BY user_id, start_date;

Executed against a 4-row subscriptions sample for two users: user 1 has three subscriptions (Jan 1 to Mar 1, Mar 20 to Jun 1, Sep 1 to Oct 1). The first gap is 19 days (Mar 1 to Mar 20), labeled resumed; the second gap is 92 days (Jun 1 to Sep 1), labeled churned; the third row has no next subscription, so next_start_date and gap_days are NULL and status is churned. User 2's single subscription also has no next row and is labeled churned.

The LTV variant, executed against purchases(user_id, purchase_date) with user 1 buying on Jan 1, Jan 15, and Mar 1, 2024, and a snapshot date of Apr 1, 2024 (2024 is a leap year, so February runs to the 29th; the 46-day gap below only reproduces with that extra day): days_to_next_purchase is 14 (Jan 1 to Jan 15) and 46 (Jan 15 to Mar 1); the Mar 1 purchase has next_purchase_date = NULL and, computed against the Apr 1 snapshot, days_observed_since_this_purchase = 31. That last row is the right-censored one: 31 days with no repeat purchase yet is a very different signal from "31 days and counting, still nothing," and both are different again from a user whose gap of 46 days between purchases is fully observed.

Trade-offs & pitfalls

• The resumed/churned gap threshold is a business decision, not a mathematical one; validate 30 days against the actual distribution of real reactivation gaps rather than hardcoding a round number.
• Conflating "no next row yet" with "will never return" quietly turns an LTV or churn feature into one that systematically under-predicts value for your newest active users, since they have had the least time to generate a "next" row.
• Overlapping subscriptions (next_start_date < end_date) produce a negative gap; decide explicitly whether that counts as resumed, gets clamped to 0, or is flagged as a distinct overlap state, rather than letting it silently pass the <= 30 check.
• LEAD avoids a full self-join scan for this pattern, but still needs an index on (user_id, start_date) (or purchase_date) to stay cheap on large tables, since the engine still has to sort or seek within each partition.