DoorDash Staff Data Scientist Interview Preparation Guide

Direct answer

Build a multiplicative decomposition tree (revenue equals the product of its drivers), then use a log-space decomposition to split the percentage decline exactly across components, and a dollar-level counterfactual (or Shapley-value averaging, for an exact version) to translate each component's share into dollars. The log method is exact for percentages; the simple dollar counterfactual is only a first-order approximation and leaves a residual when components move together.

Structured elaboration

The tree

graph TD
  A[Revenue] --> B[Traffic]
  A --> C[Conversion rate]
  A --> D[Average order value]
  A --> E[Repeat-purchase frequency]

Revenue = Traffic x Conversion Rate x Average Order Value x Repeat-purchase frequency (a retention-driven term: how many times a converting customer buys again within the quarter). Any additional multiplicative driver is added the same way, as one more term in the sum below.

Two attribution methods

1. Log-decomposition (exact, additive): since revenue is a product of factors, the log of revenue is a sum of the logs of the factors. The percentage change in each factor's log, divided by the total log change, gives an exact percentage attribution that always sums to 100%.
2. Dollar counterfactual (approximate): hold every other factor at its baseline value and swap in the new value for one factor at a time. This gives a dollar figure per factor, but when two or more factors move simultaneously, the sum of these first-order dollar contributions will not exactly equal the total dollar delta; the gap is an interaction term. Shapley-value averaging (averaging the marginal contribution of each factor across all possible orderings) removes this residual if an exact dollar split is required.

Worked example

Baseline quarter (Q0): Traffic $T_0 = 500{,}000$ sessions, Conversion $C_0 = 4.0\%$, Average order value $A_0 = \$120$.

Current quarter (Q1): $T_1 = 480{,}000$ (down 4.0%), $C_1 = 3.8\%$ (down 5.0% relative to $C_0$), $A_1 = \$118$ (down 1.67%).

Log decomposition (natural log of each factor's ratio):

Dividing each term by the total gives the percentage-of-decline attribution: Traffic 37.5%, Conversion 47.1%, Average order value 15.4% (sums to 100%).

Dollar counterfactual (holding other factors at Q0 baseline):

The $8,320 gap (about 3.4% of the total delta) is the interaction term: three factors declined simultaneously, and the first-order method double-counts part of that overlap. Reporting the log-based percentage split alongside this residual, rather than silently forcing the dollar figures to add up, is the honest way to present this.

Trade-offs & pitfalls

• The multiplicative decomposition assumes independence between factors; when components move together (a price hike raising average order value while suppressing conversion, for example), a naive first-order dollar attribution misallocates the interaction, exactly as shown by the residual above.
• The log-based percentage split is exact and easy for stakeholders to reason about ("who moved the needle most"), but it does not itself hand you a dollar figure per driver; pair it with the counterfactual or Shapley method when a dollar amount is required.
• Correlated changes need segment-level drill-down (channel, geography, cohort) to find a shared underlying cause rather than treating each branch of the tree as independent.
• A decomposition tree explains where a change occurred, not why; pair it with root-cause investigation, data-quality checks, the pricing and promotion calendar, and competitive or seasonal context, before presenting conclusions to stakeholders.

Direct answer: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) is the exact, standard way to compute a median in engines that support ordered-set aggregates (Postgres, Snowflake, BigQuery, DuckDB): it sorts the group and interpolates between the two middle values. Engines without it (MySQL, SQLite, older warehouses) need a manual ROW_NUMBER() workaround that ranks each group and averages the middle row (even count) or takes the single middle row (odd count). Switch to an approximate, sketch-based quantile function once the exact sort-per-group computation becomes the bottleneck at scale, and tell the stakeholder plainly: "this number is very close but not exact, and it will stay fast as the data grows."

Structured elaboration

Approach 1: exact, ordered-set aggregate

PERCENTILE_CONT is an ordered-set aggregate function, not a plain window function. It always needs WITHIN GROUP (ORDER BY ...) and a GROUP BY (or no GROUP BY at all for one global median). PostgreSQL's documentation is explicit that ordered-set aggregates cannot be used as window functions with OVER (...); DuckDB rejects the same syntax outright. On PostgreSQL and DuckDB specifically, if you need a per-partition median attached to every row (window-style), you compute the aggregate in a subquery or CTE (common table expression, a named, reusable subquery introduced with WITH) and join it back, since neither engine lets you bolt OVER (PARTITION BY ...) directly onto PERCENTILE_CONT. That restriction is not universal: Snowflake and Redshift both document PERCENTILE_CONT(...) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...) as valid windowed syntax, so on those two engines the per-partition median can be a single expression with no join-back step.

Approach 2: manual ROW_NUMBER() workaround (portable, works anywhere)

Rank each row within its group by the target column, count the group size, and average the row(s) sitting at the middle rank(s):

WITH numbered AS (
  SELECT
    category_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY amount) AS rn,
    COUNT(amount) OVER (PARTITION BY category_id) AS cnt
  FROM transactions
  WHERE amount IS NOT NULL
)
SELECT category_id, AVG(amount) AS median_amount
FROM numbered
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEILING((cnt + 1) / 2.0))
GROUP BY category_id;

For an odd group size the two FLOOR/CEILING picks land on the same rank, so AVG of one value is just that value; for an even group size they land on the two middle ranks and AVG interpolates the way PERCENTILE_CONT does. NTILE(2) is sometimes mentioned in the same breath as this workaround, but it is a coarser tool: it splits a group into equal-sized buckets and tells you which bucket a row falls in, it does not interpolate a boundary value, so it is the right primitive for quartile/decile bucketing, not a precise substitute for a point-estimate median.

Approach 3: approximate, sketch-based quantile estimation

Once a partition is large enough that sorting it (the O(n log n) step both approaches above rely on) means spilling to disk or holding the whole group in memory, switch to a probabilistic sketch such as t-digest, a compact, mergeable summary structure that approximates quantiles instead of computing them exactly. These structures process the data in one streaming pass, keep a small, bounded amount of state regardless of how many rows feed into it, and answer a quantile query with a small, known error bound instead of an exact sort. Every major analytical warehouse ships a built-in function for this: DuckDB's APPROX_QUANTILE, Snowflake's APPROX_PERCENTILE (built on a t-digest variant), BigQuery's APPROX_QUANTILES (returns an array of boundary values), and Redshift's APPROXIMATE PERCENTILE_DISC.

When to switch, and how to say it to a stakeholder

Switch when the exact computation is measurably the bottleneck: a group so large that the sort spills to disk, a high-cardinality GROUP BY key producing thousands of small sorts, or a dashboard that needs to stay responsive as the underlying table grows unbounded. Do not switch pre-emptively on a small or medium table just because "approximate" sounds more scalable; exactness is free until it isn't. When a stakeholder asks for "the median" and you plan to serve an approximate one, say what changes in plain terms: the number will be extremely close to the true median (typically within a fraction of a percent for standard sketch sizes) and it will stay fast no matter how much data accumulates, versus an exact number that is guaranteed correct but gets slower, and eventually a background batch job, as volume grows. If the number feeds a regulated or audited report, keep it exact; if it feeds a live operational dashboard, approximate is almost always the right trade.

Extension: exact-vs-approximate distinct counts

The same tension shows up one level over, on COUNT(DISTINCT user_id) for a daily active-users metric. An exact distinct count forces the engine to track every value it has already seen (a hash set or a sort-and-dedupe pass), so memory and compute scale with the number of distinct values, not just the row count, and a single query with several COUNT(DISTINCT ...) metrics can be especially expensive because each one needs its own tracking structure. HyperLogLog-style sketches solve it the same way t-digest solves quantiles: hash each value, keep a small fixed-size register array (kilobytes, not megabytes, regardless of whether the true distinct count is a thousand or a billion), and estimate the cardinality from the distribution of leading zero-bits observed. The error shrinks as the sketch's register count grows, and standard sizes comfortably keep the relative error to a low single-digit percentage, which is normally far tighter than a stakeholder needs for a trend line. DuckDB exposes this as APPROX_COUNT_DISTINCT; BigQuery, Snowflake, and Redshift ship equivalents (BigQuery additionally exposes the raw HLL_COUNT.* sketch functions so you can store and merge partial sketches across days instead of recomputing from scratch).

Worked example

Ran against a real transactions(category_id, amount) table in DuckDB, category_id = '1' has an odd count of five rows (10, 20, 30, 40, 100), category_id = '2' has an even count of four rows (5, 15, 25, 35):

-- Approach 1: exact, ordered-set aggregate
SELECT category_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM transactions GROUP BY category_id;

Category 1's median is the literal middle value (30). Category 2 has no single middle value, so PERCENTILE_CONT interpolates between the two middle values 15 and 25, giving (15+25)/2 = 20. The manual ROW_NUMBER() query from Approach 2, run against the same table, returned identical results (30 and 20), confirming the two exact methods agree. APPROX_QUANTILE(amount, 0.5) also returned 30 and 20 on this table, because the demo dataset is far too small for a sketch's approximation error to show up. The whole point of switching to a sketch only pays off at the row counts where an exact sort would actually hurt, which a nine-row demo table cannot honestly simulate; the sketch's real behavior is a bounded-error, bounded-memory summary, not a different answer on small data.

For the distinct-count extension, an active_users(event_date, user_id) table with 3 distinct users on day one and 4 distinct users on day two returned identical results from COUNT(DISTINCT user_id) and APPROX_COUNT_DISTINCT(user_id) for the same reason: at single-digit cardinality there is nothing for a sketch to approximate away.

Key points

• PERCENTILE_CONT is an ordered-set aggregate: exact, needs WITHIN GROUP and GROUP BY, and (verified against PostgreSQL's own documentation and by direct execution) cannot take an OVER (PARTITION BY ...) clause the way a true window function can.
• PERCENTILE_CONT silently drops NULL values from the ordering before computing the median (verified: a group of 10, 20, NULL, 30 returned a median of 20, the same as if the NULL were never there).
• NTILE buckets rows into N groups; it is a bucketing tool, not an interpolating median calculator.

Complexity

Both exact approaches require a full sort of each group: O(n log n) per group, and the whole group must be materialized before the middle value(s) can be identified, so memory scales with the largest group's size. Sketch-based approximate methods process each row once, O(n) total, using a fixed, small amount of memory per sketch regardless of group size, which is exactly what makes them viable when the exact sort would spill to disk.

Edge cases

• Empty group: both PERCENTILE_CONT and the ROW_NUMBER() approach return NULL (no rows to rank).
• NULL values in the target column: excluded from the ordering before the median is computed; make sure cnt in the manual approach is a NULL-excluding count (COUNT(amount), not COUNT(*)) or the rank-matching math drifts.
• Ties at the middle rank(s) are handled correctly by both exact approaches since they operate on sorted position, not on distinct values.

Trade-offs & pitfalls

The trap most candidates miss is reaching for NTILE as if it were a median shortcut; it answers a different question (which bucket) and does not interpolate. The trap on the approximate side is presenting a sketch-based number as if it were exact without saying so: a dashboard KPI silently switching from exact to approximate after a "performance fix" is the kind of change that erodes stakeholder trust in the numbers, even when the approximation itself is well within an acceptable error band. State the switch and its error characteristics explicitly rather than letting it hide inside a query rewrite.

Direct answer

Keep a stalled dependency peer-to-peer as long as direct conversation is still making progress. Escalate when you hit a concrete trigger: a scope change that neither side can unilaterally absorb, genuinely conflicting priorities that only someone with visibility into both roadmaps can arbitrate, or a hard deadline-driven blocker where peer-to-peer conversation has already stalled.

Framework

Default: work it peer-to-peer. Most stalls are under-communication or unclear ownership, and a direct conversation or a short written proposal usually unsticks them without anyone else getting involved.

Concrete triggers to escalate.

• Scope change: the fix now requires work neither team budgeted for, and only a manager can reprioritize that.
• Conflicting priorities: both sides are acting rationally from their own team's goals, and the trade-off needs someone with visibility into both roadmaps to arbitrate.
• Hard blocker with a deadline: a fixed external date is genuinely at risk, and peer-to-peer conversation has already stalled past a reasonable window, for example no movement after two direct attempts over several days.
• Repeated pattern: the same kind of stall keeps recurring with the same team, which means the real issue is the working relationship or process, not this one dependency.

What to bring when you escalate. A short brief: what's blocked, what you've already tried peer-to-peer, the realistic options and their trade-offs, and the specific decision you need.

Worked example (applying the criteria)

Situation: your team's deliverable needs a schema change from another team that they've deprioritized for two weeks despite two direct requests.
Applying the criteria: this isn't just a communication gap, direct conversation was already tried twice with no movement. It's a conflicting-priorities case, the other team's roadmap has no room for this without reprioritizing something else, combined with a hard blocker, a fixed external deadline in three weeks that this schema change sits on the critical path for (meaning if this dependency slips, the final deadline slips by the same amount, unlike a dependency with buffer to absorb delay).
Action: escalated to the shared manager with a one-page brief covering what's blocked, the two peer-to-peer attempts and their outcome, and two options: the other team reprioritizes one sprint of work, or your team ships a temporary workaround with known limitations, along with the deadline risk if neither happens within the week.
Result: the shared manager reprioritized one sprint item, unblocking the schema change with two weeks to spare before the deadline. Both teams also agreed to flag scope-affecting asks earlier next time, so the same dependency doesn't reach this point again.

Trade-offs and pitfalls

• Escalating too early over normal friction burns trust and reads as an inability to work horizontally.
• Escalating too late, repeatedly trying peer-to-peer past the point it's actually working, puts the deadline at real risk and looks like poor judgment in hindsight.
• A vague escalation with no options and no specific ask wastes the leader's time compared with a brief that names the decision needed.

Direct answer

Event-based analytics models the world as a stream of discrete, named actions a user takes (clicked, purchased, viewed), each carrying its own properties, while pageview- or session-based analytics models the world as page loads grouped into sessions, with actions inferred indirectly from which pages were loaded and in what order. The two imply genuinely different data models, not just different tooling.

Structured elaboration

In an event-based model, the atomic unit is the action itself: a "purchase" event fires with properties like amount and item, independent of any specific page. This makes it straightforward to answer questions about specific in-product actions, including actions that happen without a page reload, such as an in-app interaction inside a single-page application or a native mobile screen. The disadvantage is that it requires deliberate, ongoing engineering investment: every action worth analyzing has to be explicitly instrumented, named, and given a stable schema, and gaps in that instrumentation become gaps in what can be analyzed.

In a pageview-based model, the atomic unit is the page load, and sessions are built up from a sequence of page loads within an inactivity window. This is cheap to get broad coverage from, since a basic page-load tag on every page captures something without any per-action engineering work, but it is much weaker at answering questions about what a user actually DID on a given page, since a page load says nothing about what was clicked, filled in, or ignored within it.

A user-behavior question best answered by the event-based approach is something like "what fraction of users who opened the settings panel actually changed their notification preference," since that is an in-page action with no corresponding page load. A question best answered by the pageview-based approach is something like "what is the typical navigation path visitors take through the site before leaving," since that is fundamentally about sequences of pages rather than in-page actions.

Worked example

Consider a single-page checkout flow with three visible screens (cart, shipping, payment) that never triggers a full page reload, plus a "save for later" action available from the cart screen. A pageview-based analytics setup, tagging only on page load, would see exactly one page load for the entire flow and would have no way to tell whether "save for later" was ever clicked, since that action does not correspond to a new page. An event-based setup instrumenting cart_viewed, save_for_later_clicked, shipping_step_viewed, and payment_step_viewed as distinct events would correctly capture that a user reached the cart, clicked save-for-later, and never advanced further, giving a materially different and more accurate picture of where the drop-off actually happened than the pageview-only view, which would simply record one page visit with no further detail.

Trade-offs and pitfalls

A common mistake is assuming an event-based system automatically supersedes a pageview-based one; in practice, many products run both, using pageviews for cheap, broad top-of-funnel visibility and events for the specific in-product actions the team most needs to understand. The pitfall to watch for is treating a page-load count as a proxy for engagement inside modern single-page applications, where a single page load can hide an arbitrary amount of real user activity that never shows up without explicit event instrumentation.

Direct answer

Batch, stochastic, and mini-batch gradient descent all use the same update rule, they only differ in how many examples' gradients get averaged before each step. Batch GD averages over the entire dataset (low noise, expensive per step), stochastic GD uses one example at a time (high noise, cheap per step), and mini-batch splits the difference. Feature scaling matters for all three because an unscaled feature distorts the shape of the loss surface itself, which no choice of batch size fixes.

Structured elaboration

$w \leftarrow w - \eta\, \nabla_w L(w)$

is the shared update rule; what changes between the three variants is what $\nabla_w L(w)$ is computed from:

Why feature scaling matters for all three: the gradient of a linear model's loss with respect to a weight is proportional to that feature's magnitude, so a feature with a much larger numeric range dominates the gradient direction, making the loss surface elongated and ill-conditioned. This slows convergence (forcing a smaller learning rate) regardless of whether you're averaging over the full dataset, one example, or a mini-batch, the batch size changes the noise in the gradient estimate, not the shape of the underlying loss surface.

Worked example

Simple linear model $\hat{y} = wx$ with per-example squared loss, gradient $\nabla_w L_i(w) = 2 x_i (w x_i - y_i)$. Four training points: $(1,3), (2,5), (3,7), (4,9)$, and current weight $w = 1$.

Batch gradient (average of all four): $(-4 -12 -24 -40)/4 = -20$

Single-example (SGD) gradients range from -4 (point 1) to -40 (point 4), a 10x spread depending purely on which point got sampled.

Mini-batch of size 2: points {1,2} average to $(-4-12)/2 = -8$; points {3,4} average to $(-24-40)/2 = -32$, a 4x spread, narrower than single-example SGD but still noisier than the fixed batch value of -20.

With learning rate $\eta = 0.01$, the batch update moves $w$ from 1 to $1 - 0.01 \times (-20) = 1.2$, a single-example update using only point 1 would instead move it to $1 - 0.01 \times (-4) = 1.04$, a visibly smaller and less representative step, which is exactly the variance/noise trade-off in numbers.

Trade-offs & pitfalls

• SGD's noise is a feature, not just a bug: it can help escape shallow local minima or saddle regions, but it requires a decaying learning rate or momentum/Adam to actually settle near a minimum rather than bouncing around it indefinitely.
• Mini-batch size interacts with hardware, not just statistics: very small batches underutilize GPU parallelism, very large batches need learning-rate scaling and warmup to avoid degraded generalization.
• Feature scaling is not optional for any of the three variants, without it, you're forced into a smaller learning rate than the well-scaled problem would need, which slows every variant proportionally.
• Pitfall: tuning batch size as if it were purely a speed knob. It also changes the effective noise in the optimization, which affects both convergence speed and, for non-convex problems, the character of the minimum you land in.

Direct answer. Make deletion tractable by isolating personal identifiers into a single dedicated table keyed by a pseudonymized ID from the start, so every other system, including the data lake, the warehouse, streaming topics, derived and denormalized tables, and ML (machine learning) training sets, references the pseudonym instead of storing the real identifier. A deletion request then becomes deleting or crypto-shredding one row plus fanning out a tombstone event, instead of hunting through every table in the company. For systems that can't practically delete in place, such as backups and some ML training artifacts, apply irreversible pseudonymization or crypto-shredding, and produce an auditable receipt for every system confirming completion, not just a log line saying the job ran.

Schema pattern that makes this tractable. Assign every subject a stable pseudonymized ID, a random or hashed identifier with no reversible mapping exposed outside the identity system, at first contact. Store the mapping between the pseudonym and any real identifying attributes (name, email, phone, address) in exactly one isolated table, access-controlled separately from everything else. Every other table, topic, and derived dataset references the pseudonym, never the real identifier directly; if a downstream system needs to display a real name, it looks it up from the isolated table at render time rather than copying the name into its own storage. Deletion then reduces to deleting or crypto-shredding (discarding the encryption key so the ciphertext becomes permanently unreadable) the one row in the isolated table, after which the pseudonym everywhere else becomes an opaque, non-personal identifier.

Identifying every place the data lives, and how each is handled

• Data lake, raw and curated zones: tag every dataset containing the isolated PII (personally identifiable information) table or a denormalized copy of it at ingestion; a deletion event triggers a rewrite of affected partitions, not a soft flag.
• Warehouse: derived tables should already reference the pseudonym only, so the mapping table gets the delete; any warehouse table that denormalized real attributes directly needs its own remediation job.
• Backups and snapshots: cannot practically be edited in place. Either let old backups expire naturally within a documented retention window, disclosed as part of the deletion SLA (service-level agreement), meaning the data becomes irretrievable in production but persists in an inaccessible backup until it rolls off, or encrypt the isolated PII table with a per-subject key and crypto-shred the key so the backup contains only permanently unreadable ciphertext.
• Streaming topics: publish a deletion or tombstone event keyed by the pseudonym to a compacted topic so consumers apply the same removal; topics that already stored real attributes directly need a targeted purge or key rotation, which is exactly why the isolation pattern should be enforced at the producer level going forward.
• Derived, aggregated, and denormalized tables: an aggregate that already summed or counted a subject's contribution can't be un-summed for one individual without recomputation. Aggregates where the subject's contribution isn't separable, for example "total monthly signups" among thousands, generally don't need per-subject reconstruction since no personal data is retrievable from them; but any denormalized table that copied the subject's raw attributes directly, the exact anti-pattern the isolated-table design avoids, must be identified and remediated as its own class of hard case, because the schema pattern didn't protect it in the first place.
• ML training sets: a model trained on data containing the subject's attributes is the hardest case, since deleting the raw training row doesn't remove what the model already learned. If the training pipeline referenced the pseudonym and looked up attributes at training time, deletion cascades the same way as any other pseudonym-keyed dataset. If raw PII was baked directly into stored training files, those artifacts must be identified and deleted, and the affected model scheduled for retraining on its normal cadence; exact "unlearning" from an already-trained model without retraining isn't something that can be reliably promised, so a scheduled retrain, not an in-place model edit, is the honest answer.

Verifiable proof for an auditor. A central deletion service issues a deletion event with a unique request ID and records which systems must acknowledge. Each downstream system, lake job, warehouse job, streaming consumer, backup rotation, writes a signed completion receipt back (system name, request ID, timestamp, rows or partitions affected) to an audit log the service owns. That audit log, not each team's word, is what's shown to an auditor: request R, acknowledged complete by N of N required systems, with receipts. Periodically, not just per request, a scan across systems for the deleted pseudonym confirms it doesn't resurface, for example from a delayed backfill job re-ingesting an old file, and any resurfacing is treated as its own incident.

flowchart TD
    A[Deletion request: subject S] --> B[Deletion service]
    B --> C[Isolated PII table: delete or crypto-shred row]
    B --> D[Tombstone event: pseudonym P]
    D --> E[Data lake jobs]
    D --> F[Warehouse jobs]
    D --> G[Streaming consumers]
    D --> H[ML training pipeline: flag for retrain]
    E --> I[Signed completion receipt]
    F --> I
    G --> I
    H --> I
    I --> J[Audit log: request R, N/N acknowledged]

Worked example. Subject S requests deletion. The deletion service deletes S's row from the isolated pii_identity table (keyed by pseudonym P) and publishes a tombstone for P to a compacted topic. A warehouse job that joined orders to pii_identity for display purposes now resolves no name for P, and needs no rewrite, since the orders table itself only ever stored P, never the name. A denormalized legacy export table that copied customer_name directly two years ago, before the isolation pattern was enforced, is flagged separately by the catalog tag and requires a one-off remediation job. The ML training pipeline had ingested pii_identity fields directly into a stored feature file for a churn model; that file is identified, the row purged, and the churn model queued for its next scheduled retrain rather than promised an immediate in-place fix. Each of the four affected systems, warehouse, streaming, legacy export, and ML pipeline, writes a completion receipt, and the deletion service shows 4/4 acknowledged in the audit log as the auditor-facing proof.

Trade-offs and pitfalls

• The isolated-PII-table pattern only works going forward and for systems built to reference the pseudonym; retrofitting it onto years of tables that denormalized real attributes directly is a real migration project, not a policy memo.
• Backups are the honest limitation almost every design has to disclose: either accept a documented backup-expiry window as part of the deletion SLA, or invest in per-subject encryption keys so backups can be crypto-shredded, which adds real key-management overhead.
• The most common wrong turn is declaring deletion done when the primary systems are clean but derived, denormalized copies and ML artifacts were never inventoried; a completion receipt from every known system is only as good as the catalog's completeness in knowing which systems exist.

For a video streaming product, the right engagement metrics differ by time horizon because a single number can't distinguish a good single session from a habit, so tracking one metric per horizon (daily, weekly, monthly) catches problems a single aggregate would hide.

Three engagement metrics by horizon

Why one horizon alone misleads

Daily minutes watched can spike from a single viral release without indicating any lasting change in habit; weekly days-active can look healthy from repeat short check-ins that never lead to satisfying, completed viewing; only the monthly completion metric would catch a catalog that hooks people briefly but rarely delivers a satisfying full watch, which is the actual thing a streaming business needs for long-term retention.

Trade-offs and pitfalls

Using only the daily metric invites over-reacting to noise (weekend viewing patterns, one big release) as if it were a durable trend; using only the monthly metric is too slow to catch a real-time regression from a broken recommendation algorithm. The three horizons are meant to be read together, not to replace each other.

Redesign the input path
I would separate expensive preprocessing from the training loop and make the input stream sharded, cached, and deterministic.

Main changes

• Compact many small object-store files into larger shards so reads are sequential instead of chatty.
• Precompute expensive transforms once, then store the transformed output or an intermediate cache.
• Use a manifest that records shard order, sample IDs, and versioned transform code.
• Prefetch data to local disk or memory so GPUs do not wait on network reads.
• Keep lightweight, deterministic transforms in the training worker so results are reproducible.

Worked example
If the dataset is 10,000 files of 2 MB each, that is 20 GB spread across many requests. Repacking into 200 shards of 100 MB keeps the same 20 GB, but cuts file open and listing overhead dramatically.

Why this helps
The GPUs stay busy because decoding and network latency happen ahead of time. Reproducibility comes from versioned manifests and fixed seeds. Debuggability comes from keeping raw sample IDs so a bad example can be replayed exactly.

This is usually the highest leverage change when preprocessing time dominates training time.

Direct answer: Churn and segmentation feature design at scale needs to combine behavioral counts/rates/recency, cross-source interactions, and time-based signals computed from usage logs, support tickets, and demographics, with an explicit leakage check before trusting any offline lift, since churn labels are especially prone to accidentally encoding future information.

Structured elaboration: Eight concrete engineered features, with intuition and how each is computed at scale (roughly two million rows of mixed numeric, high-cardinality categorical, timestamp, and free-text data):

1. Days since last login/usage event (recency) - intuition: a lapsing user's engagement gap widens before they formally churn. Computed via a vectorized groupby-max on the timestamp column per customer, not a row-by-row loop, which is essential at two million rows.
2. Login/event frequency over trailing 7/30/90-day windows - intuition: a declining frequency trend is often more predictive than any single-point count. Computed via grouped, windowed aggregation (e.g. groupby(customer_id).rolling() or a windowed SQL aggregate).
3. Usage-intensity trend (slope of a simple linear fit or week-over-week ratio of event counts) - intuition: distinguishes "still active but declining" from "steady at a low level," which behave differently for churn.
4. Support ticket count and recency - intuition: a recent spike in support contact correlates with dissatisfaction, but must be computed carefully to avoid the leakage case below.
5. Support ticket sentiment or category distribution (from free-text ticket bodies, e.g. via a lightweight TF-IDF or keyword-tagging pass) - intuition: ticket TONE (frustrated vs. neutral) often separates at-risk from routine contacts even at similar ticket counts.
6. Plan/product tenure (time since signup or since last plan change) - intuition: churn risk is often U-shaped or front-loaded relative to tenure.
7. High-cardinality categorical encoding of plan or product identifier - using target encoding (with proper out-of-fold computation to avoid leakage) or frequency encoding rather than naive one-hot, since a high-cardinality plan/product ID field would otherwise blow up the feature space.
8. Cross-source interaction: declining usage AND a recent support contact (an explicit interaction feature, e.g. a flag or product of the two normalized signals) - intuition: the combination is a stronger churn signal than either usage decline or ticket volume alone, since users who complain but keep using the product, or who go quiet without complaining, behave differently than users doing both.

At roughly two million rows, computing these efficiently means vectorized, grouped aggregation (pandas groupby/window functions, or an equivalent SQL/Spark aggregation) rather than row-by-row processing, and encoding the high-cardinality categoricals (plan or product identifiers) with target or frequency encoding rather than naive one-hot encoding, which would otherwise create an unmanageably wide sparse matrix.

Whether to standardize or bin a behavioral signal depends on the downstream model and the signal's distribution: a heavily skewed count (most customers have very few support tickets, a small tail has many) often benefits from binning or a log-style transform for a linear model, while a tree-based model is largely indifferent to the raw scale and doesn't need standardization at all.

Detecting leakage specifically for churn: check whether any candidate feature (feature 4 and 5 above are the highest-risk ones) is computed using data from AFTER the point the churn decision would have actually been observable. A support ticket opened in response to a cancellation the customer had already decided on is a classic hidden leak, since it looks predictive but is really a symptom of the outcome, not a cause.

Worked example: A "days until account closure" style feature (however indirectly encoded, e.g. a support-ticket category that only exists post-cancellation) would show enormous offline lift and be completely unusable in production, since at prediction time you don't yet know whether the customer will churn; catching this requires explicitly auditing what information was genuinely available BEFORE the churn decision point for every candidate feature, not just checking whether the feature "looks reasonable."

Trade-offs and pitfalls: A common trap at this scale is trusting an offline metric improvement without an out-of-time evaluation; a feature that looks powerful on a random split can be capturing something that's specific to the historical period (a promotion that ran during part of the data) rather than a durable churn signal.

Overfitting: a model captures noise or idiosyncrasies in the training data and performs much better on training than on unseen data. Underfitting: a model is too simple to capture the underlying signal and performs poorly on both training and validation.

Learning curves: plot model performance (e.g., accuracy or RMSE) on training and validation sets versus training set size.

• Overfitting signature: training performance is high, validation performance is much lower; gap persists as data grows. Validation may improve slowly with more data. Worked example (train/validation accuracy at increasing training-set size n): n=100: train=99%, val=58%; n=500: train=98%, val=61%; n=1000: train=97%, val=62%; n=2000: train=97%, val=63%. The training curve sits near-flat and high the whole way, the validation curve creeps up only slowly, and the gap between them (roughly 35-40 points) barely narrows even as n quadruples: that persistent, wide, flat gap is exactly the "training error stays low while validation error stays high and roughly flat" pattern the question describes.
• Underfitting signature: both training and validation performance are poor and close to each other; adding more data doesn't help much. Worked example: n=100: train=64%, val=61%; n=500: train=66%, val=64%; n=1000: train=67%, val=65%; n=2000: train=67%, val=66%. Both curves sit low from the start, stay within a couple of points of each other throughout, and neither one climbs meaningfully as n grows: the model has hit a capacity ceiling that more data can't fix, unlike the overfitting case where the gap (not the level) was the problem.

Practical BI pipeline changes

• If I detect overfitting: add regularization or simplify the model used in automated reports (e.g., switch from a high-cardinality decision tree to a regularized logistic regression or apply L1 feature selection). Also enforce cross-validation in model training and reduce dimensionality (aggregate categorical levels, drop redundant features) before publishing predictions to dashboards. Applied to the worked example above, adding L2 regularization and dropping redundant features would be expected to pull training accuracy down from the high-90s toward the mid-80s while lifting validation accuracy from the low 60s toward the low-to-mid 70s, narrowing the gap rather than closing it in one step.
• If I detect underfitting: enrich features and increase model capacity: add derived features (time-based aggregations, interaction terms), loosen regularization, or use a more flexible model (e.g., gradient boosted trees) in the modeling stage. Re-run validation and update ETL to include the new features so dashboards reflect improved predictions. Applied to the worked underfitting example above, swapping in gradient boosted trees and adding derived features would be expected to lift both curves together, e.g. training accuracy from ~67% toward the mid-80s and validation from ~66% toward the high-70s, since the ceiling here was capacity, not overfitting.

These changes balance predictive accuracy with interpretability and operational constraints typical in BI deliverables.