SQL Questions in ML Interviews

Top 4 SQL Patterns Tested in ML Rounds

1. Rolling Window Aggregations (Feature Engineering)

Compute rolling 30-day user purchase count and average order value:

SELECT
  user_id,
  event_date,
  purchase_amount,
  COUNT(purchase_id) OVER(
    PARTITION BY user_id
    ORDER BY event_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS user_30d_purchase_count,
  AVG(purchase_amount) OVER(
    PARTITION BY user_id
    ORDER BY event_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS user_30d_avg_spend
FROM user_purchases;

2. User Cohort Retention Analysis

Calculate Day-7 retention rate per signup cohort:

WITH Signups AS (
  SELECT user_id, DATE(signup_timestamp) AS signup_date
  FROM users
),
Logins AS (
  SELECT DISTINCT user_id, DATE(login_timestamp) AS login_date
  FROM user_logins
)
SELECT
  s.signup_date,
  COUNT(DISTINCT s.user_id) AS total_signups,
  COUNT(DISTINCT l.user_id) AS day_7_retained_users,
  ROUND(COUNT(DISTINCT l.user_id) * 1.0 / COUNT(DISTINCT s.user_id), 4) AS day_7_retention_rate
FROM Signups s
LEFT JOIN Logins l
  ON s.user_id = l.user_id
  AND l.login_date = s.signup_date + INTERVAL '7 DAY'
GROUP BY s.signup_date
ORDER BY s.signup_date DESC;

3. Point-in-Time Correct Feature Lookup

Extract historical user credit score as of transaction date:

SELECT
  t.transaction_id,
  t.user_id,
  t.transaction_timestamp,
  f.credit_score
FROM transactions t
LEFT JOIN LATERAL (
  SELECT credit_score
  FROM user_credit_history h
  WHERE h.user_id = t.user_id
    AND h.updated_at <= t.transaction_timestamp
  ORDER BY h.updated_at DESC
  LIMIT 1
) f ON TRUE;

4. Handling NULLs & Array Un-nesting

Safe default replacement and JSON/Array un-nesting (BigQuery / Databricks):

SELECT
  user_id,
  COALESCE(total_clicks, 0) AS safe_clicks,
  tag_name
FROM user_activity
CROSS JOIN UNNEST(interest_tags) AS tag_name;

Say this out loud

"SQL in ML interviews focuses on feature extraction and cohort metrics. We use window functions OVER(PARTITION BY ... ORDER BY ... ROWS BETWEEN) to compute rolling historical feature aggregations without collapsing row counts. We use point-in-time lateral joins or ASOF JOINs to prevent data leakage, and handle missing values via COALESCE to generate clean model inputs."

Follow-ups to expect

• What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()? ROW_NUMBER() assigns sequential integers (1,2,3,4). RANK() leaves gaps for ties (1,2,2,4). DENSE_RANK() does not leave gaps for ties (1,2,2,3).
• How do you optimize slow SQL query aggregation over billions of rows? Partition tables by date/timestamp, cluster by user_id, use incremental materialized views, avoid SELECT *, and pre-aggregate features in distributed engines like Spark or Snowflake.