Ace data science interviews — from SQL to deep learning

SQL & Databases

Complex queries, window functions, CTEs, query optimization, indexing strategies, normalization vs denormalization, partitioning.

"Write a query to find the top 3 products by revenue in each category for the last 30 days."

WITH ranked AS (SELECT category, product, SUM(revenue) as total_rev, ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) as rn FROM orders WHERE order_date >= CURRENT_DATE - 30 GROUP BY category, product) SELECT * FROM ranked WHERE rn <= 3. Use ROW_NUMBER for exactly 3, DENSE_RANK if ties should all appear.

Machine Learning

Model selection, bias-variance trade-off, cross-validation, feature engineering, regularization, ensemble methods, neural networks.

"How would you handle a highly imbalanced dataset with 2% positive class?"

Multiple approaches: 1) Resample — SMOTE for oversampling minority, or random undersampling majority. 2) Cost-sensitive learning — increase penalty for misclassifying minority class. 3) Use metrics beyond accuracy — precision-recall AUC, F1, or Matthews correlation. 4) Ensemble — balanced random forest or EasyEnsemble. 5) Anomaly detection framing if ratio is extreme (<1%).

Statistics & Probability

Hypothesis testing, p-values, confidence intervals, Bayesian reasoning, distributions, conditional probability, central limit theorem.

"You run an A/B test and get p=0.03. Your manager says we should ship. What do you do?"

Not so fast. Check: 1) Was sample size pre-determined or did we peek? Peeking inflates false positive rate. 2) Is the effect size practically significant, not just statistically? A 0.1% lift with p=0.03 may not justify the engineering cost. 3) Did we run multiple comparisons? 20 metrics at alpha=0.05 means ~1 false positive expected. 4) Check for novelty effect — is the lift sustained over time? Recommend: run for full planned duration, apply Bonferroni correction if needed.

A/B Testing

Experiment design, sample size calculation, statistical significance, novelty effects, network effects, metric selection, guardrail metrics.

"How would you design an A/B test for a new checkout flow?"

Primary metric: conversion rate (checkout starts to purchases). Guardrail metrics: revenue per user, refund rate, page load time. Sample size: use power analysis — for 2% baseline conversion, detecting 5% relative lift (0.1% absolute) at 80% power and 5% significance needs ~160K users per variant. Randomize at user level (not session) to avoid cross-contamination. Run for minimum 2 weeks to capture day-of-week effects. Watch for SRM (sample ratio mismatch) on day 1.

Python / Pandas / NumPy

Data manipulation, cleaning, vectorized operations, merge strategies, groupby, apply vs transform, memory optimization.

"You have a 50GB CSV that doesn't fit in memory. How do you process it with Pandas?"

Options: 1) Chunked reading — pd.read_csv(chunksize=100000), process each chunk, concat results. 2) Dtype optimization — use category for low-cardinality strings, downcast numerics (int64→int32). 3) Use Dask for out-of-core parallel processing with Pandas-like API. 4) Read only needed columns with usecols parameter. 5) For SQL-like aggregations, load into DuckDB which handles larger-than-memory data natively.

Business Case / Product Analytics

Funnel analysis, cohort analysis, retention metrics, LTV calculation, metric decomposition, root cause analysis, product sense.

"Daily active users dropped 10% last week. Walk me through how you'd investigate."

Step 1: Segment — is it all users or specific cohort (new vs returning, mobile vs desktop, geo)? Step 2: Funnel — where in the funnel is the drop? Acquisition, activation, or engagement? Step 3: Time — was it sudden (likely a bug or outage) or gradual (seasonal/trend)? Step 4: External — any holidays, competitor launches, or app store changes? Step 5: Correlation — did a deploy coincide? Check error rates, latency. Step 6: Quantify — is the 10% within historical variance or a true anomaly?