Data Science and Machine Learning 101: Business Applications and Soft Skills Interview Preparation SQL Case Studies Product Sense ML Fundamentals Behavioral

What This Is

Interview preparation for data‑science roles is a focused practice of three pillars: SQL case studies, product‑sense thinking, and ML fundamentals (plus the ever‑present behavioral interview). It matters because every hiring manager wants to see that you can (1) extract and wrangle data with SQL, (2) translate a vague business problem into a concrete analytical plan, and (3) build, evaluate, and communicate a model that drives product decisions. Example: a retailer asks “Why are high‑value customers churning?” – you’ll write a SQL query to pull the last 90‑day activity, sketch a churn‑prediction product metric, train a logistic‑regression model, and explain the impact to the product team.

Key Terms & Formulas

• SELECT‑FROM‑WHERE – basic SQL pattern to retrieve rows (SELECT col FROM table WHERE condition).
• GROUP BY / HAVING – aggregate rows by a key; HAVING filters on aggregated values (e.g., HAVING COUNT(*) > 100).
• Window Functions – compute values across a “window” of rows without collapsing them (e.g., ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts)).
• Precision = TP / (TP + FP) – proportion of predicted positives that are correct; TP = true positives, FP = false positives.
• Recall = TP / (TP + FN) – proportion of actual positives captured; FN = false negatives.
• F1‑Score = 2·(Precision·Recall) / (Precision + Recall) – harmonic mean, useful when classes are imbalanced.
• AUC‑ROC – area under the Receiver Operating Characteristic curve; measures trade‑off between TPR and FPR across thresholds.
• Log‑Loss = – ∑[y·log(p) + (1‑y)·log(1‑p)] – penalizes confident wrong predictions; lower is better.
• Bias‑Variance Trade‑off – error = Bias² + Variance + Irreducible noise; high bias → underfit, high variance → overfit.
• L1 Regularization (Lasso) – adds λ·|w| to loss; drives coefficients to exact zero → feature selection.
• L2 Regularization (Ridge) – adds λ·w² to loss; shrinks coefficients uniformly, keeps all features.
• Cross‑Validation (k‑fold) – split data into k folds, train on k‑1, validate on the held‑out; average metric reduces variance of performance estimate.

Step‑by‑Step / Process Flow

1. Load & Inspect Data (SQL → pandas)
   python
df = pd.read_sql("""SELECT user_id, event_date, revenue
FROM events
WHERE event_date >= '2023-01-01'""",
con=engine)
df.head()
2. Explore & Clean – check nulls, outliers, and data types; use df.describe() and df.isnull().sum().
3. Feature Engineering – create lag features, churn flag, and categorical encodings (pd.get_dummies).
4. Train‑Test Split – stratify on the target to preserve class balance.
   python
X_train, X_test, y_train, y_test = train_test_split(X, y,
test_size=0.2,
stratify=y,
random_state=42)
5. Baseline Model & Evaluation – fit a simple logistic regression, compute AUC‑ROC and F1.
   python
model = LogisticRegression(class_weight='balanced')
model.fit(X_train, y_train)
preds = model.predict_proba(X_test)[:,1]
print('AUC:', roc_auc_score(y_test, preds))
6. Iterate: Hyper‑parameter Tuning & Validation – use GridSearchCV with 5‑fold CV, then interpret coefficients or SHAP values for product impact.

Common Mistakes

• Mistake: Pulling all columns with SELECT * and later discovering irrelevant or PII data.
  Correction: Explicitly list needed columns; add a data‑privacy check early.

• Mistake: Using accuracy on a 95 % non‑churn dataset and claiming “90 % accuracy”.
  Correction: Report precision, recall, and AUC; for imbalanced data, focus on recall or PR‑AUC.

• Mistake: Forgetting to stratify during train‑test split, leading to a test set with no churn cases.
  Correction: Always pass stratify=y to train_test_split when the target is categorical.

• Mistake: Over‑engineering features (e.g., dozens of one‑hot columns) without checking multicollinearity.
  Correction: Use variance‑inflation factor (VIF) or regularization to prune redundant features.

• Mistake: Giving a product answer that sounds “data‑driven” but lacks a concrete metric (e.g., “increase retention”).
  Correction: Tie every recommendation to a KPI (e.g., “reduce churn by 5 % → increase LTV by $X per month”).

Data Science Interview / Practical Insights

1. SQL vs. Pandas Trade‑offs – Interviewers may ask why you’d write a window function in SQL instead of loading everything into pandas. Expect to discuss data size, latency, and push‑down computation.
2. Product Sense – Be ready to articulate the impact loop: problem → metric → experiment → decision. A common trap is to jump straight to “model accuracy” without defining the business metric first.
3. ML Fundamentals – Know the difference between Bagging (reduces variance by averaging many independent learners, e.g., Random Forest) and Boosting (reduces bias by sequentially focusing on errors, e.g., XGBoost). Interviewers love a concise table comparing them.
4. Behavioral – Use the STAR (Situation, Task, Action, Result) format; quantify results (e.g., “cut data‑pipeline latency by 30 % → saved $50k per quarter”).

Quick Check Questions

1. Scenario: Your churn model has high variance on the validation set.
   Answer: Apply L2 regularization or increase bagging (e.g., Random Forest) to shrink variance.

2. Scenario: The business cares about catching every high‑value churner, even at the cost of false alarms.
   Answer: Optimize for Recall (or use a low‑threshold PR‑AUC) rather than precision.

3. Scenario: You need to compute the rolling 7‑day revenue per user in SQL.
   Answer: Use a window function: SUM(revenue) OVER (PARTITION BY user_id ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Last‑Minute Cram Sheet (10 one‑liners)

1. ⚠️ SELECT * is a red flag – always enumerate needed columns to avoid pulling PII or huge tables.
2. GROUP BY aggregates; HAVING filters on those aggregates (e.g., HAVING SUM(revenue) > 1000).
3. Window functions let you compute running totals without collapsing rows.
4. Precision = TP / (TP + FP); Recall = TP / (TP + FN).
5. F1‑Score balances precision & recall; best for imbalanced classes.
6. AUC‑ROC is threshold‑agnostic; PR‑AUC is more informative when positives are rare.
7. L1 drives coefficients to zero → built‑in feature selection; L2 shrinks all coefficients uniformly.
8. k‑fold CV (k ≥ 5) stabilizes performance estimates; keep folds stratified for classification.
9. Bagging = parallel learners (reduces variance); Boosting = sequential learners (reduces bias).
10. STAR = Situation, Task, Action, Result – the go‑to structure for behavioral answers.