Data Science Interview Questions

Start with a date spine (generate_series or a calendar table) to ensure every day has a row, even days with zero active users. LEFT JOIN the user activity table to the date spine. COUNT(DISTINCT user_id) per day gives you daily active users. Then apply AVG() with a window frame: AVG(dau) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). The date spine is the key insight. Without it, your rolling average skips zero-activity days, inflating the result. This question tests window functions, date handling, and awareness of data gaps.

Aggregate spending by user and month. Use LAG to compare each month's spending to the previous month. Flag months where spending increased. Then identify sequences of 3 consecutive increase flags. The date-minus-ROW_NUMBER trick creates groups of consecutive months. Filter groups with COUNT >= 3. This tests window functions, aggregation, and the consecutive-sequence pattern that appears in both DS and DE interviews.

A p-value of 0.04 is below the standard 0.05 threshold, so the result is statistically significant. But significance alone is not sufficient for a ship decision. Consider: (1) Is the 3% lift practically significant? A 3% lift on a 0.1% baseline is different from a 3% lift on a 50% baseline. (2) Was the sample size adequate? Two weeks might not capture weekly seasonality patterns. (3) Did they look at guardrail metrics (revenue, latency, error rates)? A CTR improvement that degrades user experience is not worth shipping. (4) Was there any p-hacking (checking results daily and stopping early)? The right answer is: statistically significant, but need to verify practical significance, check guardrails, and confirm the test ran for the pre-determined duration.

Type I error (false positive): the alert fires when there is no real data quality issue. Your pipeline data is fine, but the monitor triggers a page at 3 AM. Type II error (false negative): there is a real data quality issue, but the alert does not fire. Bad data flows downstream undetected. For a data quality system, Type II errors are usually more costly because corrupted data reaches dashboards and ML models before anyone notices. When tuning alert thresholds, you trade off between the two: tighter thresholds reduce Type II errors but increase Type I (more false alarms). This framing shows interviewers you understand statistical concepts in an engineering context.

Start with requirements: what features does the model need, how fresh must they be, and what latency does serving require? Batch features (user demographics, historical preferences) can be computed daily in Spark and stored in a feature store. Near-real-time features (session clicks, last-5-minutes behavior) require a streaming pipeline (Kafka to Flink) writing to a low-latency store (Redis or DynamoDB). The feature store should support both batch training (historical features joined to training labels) and online serving (point lookup by user_id at inference time). Discuss versioning: when you add a new feature, the model needs retraining on the same feature definition. This tests your understanding of the ML infrastructure that data engineers build and maintain.

Data drift means the distribution of input features changes over time, which can degrade model performance. Detection: compute statistical summaries (mean, median, percentiles, distribution histograms) for each feature on each batch. Compare current batch distributions to a reference window using KS tests, PSI (population stability index), or simple threshold checks on mean/variance. Handling: alert the ML team when drift exceeds a threshold, trigger model retraining if drift is persistent, and log drift metrics for retrospective analysis. As a data engineer, your responsibility is building the monitoring pipeline that detects drift and the retraining infrastructure that responds to it. You are not necessarily choosing the model architecture, but you are building the system that keeps the model healthy.

Layer multiple signals: (1) Known bot user-agents (Googlebot, Bingbot, etc.) are easy to filter in the ingestion layer. (2) Request rate limits: flag user_ids with more than N actions per minute as suspicious. (3) Session behavior: bots often have zero scroll depth, no mouse movements, and perfectly uniform time-between-clicks. (4) IP reputation: cross-reference IPs against known bot networks. Build a scoring pipeline that assigns a bot_probability to each session. Let the experimentation team choose their threshold (e.g., exclude sessions with bot_probability > 0.8). Store both raw and filtered datasets so the team can compare results with and without filtering. The key is that bot filtering is a data engineering problem that directly affects data science outcomes.

The pipeline needs to: (1) Assign users to variants deterministically (hash user_id + experiment_id to ensure consistent assignment across sessions). (2) Log exposure events (which user saw which variant, when). (3) Join exposure events to outcome metrics (clicks, purchases, engagement). (4) Compute per-experiment statistics (mean, variance, sample size per variant). For 200 concurrent tests, the join between exposures and outcomes is the bottleneck. Partition both tables by date and experiment_id. Pre-aggregate metrics daily to reduce join size. Store results in a materialized table that the experimentation UI reads. Discuss backfill: when a metric definition changes, you need to recompute results for running experiments. This tests pipeline design, data modeling, and understanding of how experimentation infrastructure works at scale.

Retention rate: the percentage of users who return to the app N days after their first visit. For 7-day retention: identify each user's first activity date. Check if they had any activity on exactly day 7 (or within a 7-day window, depending on definition). SQL: WITH first_visit AS (SELECT user_id, MIN(activity_date) AS first_date FROM activity GROUP BY user_id) SELECT first_date, COUNT(DISTINCT CASE WHEN a.activity_date = f.first_date + 7 THEN f.user_id END) * 100.0 / COUNT(DISTINCT f.user_id) AS retention_7d FROM first_visit f LEFT JOIN activity a ON f.user_id = a.user_id AND a.activity_date = f.first_date + 7 GROUP BY first_date. Discuss the definition ambiguity: strict (exactly day 7) vs window (days 7 to 13). Different companies use different definitions, and clarifying this upfront shows attention to detail.

If AOV went up but total revenue stayed flat, the number of orders must have decreased. Revenue = AOV * order_count. If AOV increases by 15% and revenue is constant, order count dropped by approximately 13%. Investigate: (1) Did a segment of low-value customers stop ordering, which would mechanically raise AOV while reducing volume? (2) Was there a price increase that drove away price-sensitive customers? (3) Did a promotion end that was generating high-volume, low-value orders? (4) Check for data pipeline issues: are all order sources still reporting? A broken ingestion pipeline for one order channel could reduce order count without the team realizing. This question tests business intuition and the habit of questioning data rather than accepting it at face value.