Post cover image

December 2, 2025

Data Engineering Interview Prep Series — Part 1: SQL Interview Challenges You Must Master in 2026

12 real SQL problems with solutions and explanations — designed exactly like interview rounds.

By Ankur Gupta

2 min read

Why SQL Still Dominates Data Engineering Interviews

Even with cloud tools, Spark, and automation, SQL remains the core language for:

  • Transformations
  • Data modeling
  • Analytics engineering
  • Real-time aggregation
  • Business logic validation

If you can't query data well, you can't engineer data well.

These problems reflect exact interview expectations — not textbook theory.

Challenge 1 — Top N per Group (Window Function)

Find top-selling product per category.

SELECT *
FROM (
  SELECT
    product,
    category,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM sales
)
WHERE rn = 1;

Tests: window functions + ranking + grouping strategy.

Challenge 2 — Sessionize User Activity

A trending advanced problem.

Group clicks into sessions when gap > 10 minutes.

SELECT
  user_id,
  click_time,
  SUM(
    CASE WHEN TIMESTAMPDIFF(MINUTE,
           LAG(click_time) OVER (PARTITION BY user_id ORDER BY click_time),
           click_time) > 10
         THEN 1 ELSE 0 END
  ) OVER (PARTITION BY user_id ORDER BY click_time) AS session_id
FROM clicks;

Tests: time logic, window functions.

Challenge 3 — Deduplicate but Keep Latest

SELECT t.*
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM users
) t
WHERE rn = 1;

Tests: real-world reconciliation problems.

Challenge 4 — Rolling Daily Revenue (7-day window)

SELECT
  date,
  SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS last_7_days_rev
FROM daily_sales;

Tests: window frame specification.

Challenge 5 — Find Anomalies

Orders placed without registered customers

SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Tests: data validation logic.

Challenge 6 — Customers Who Bought All Products

SELECT customer_id
FROM purchases
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products);

Tests: subquery + set logic.

Challenge 7 — Fill Missing Values with Latest Known

SELECT
  user_id,
  last_value(status IGNORE NULLS)
    OVER (PARTITION BY user_id ORDER BY event_time) AS filled_status
FROM events;

Tests: real-time event data cleanup.

Challenge 8 — Detect Duplicates (but don't remove)

SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Tests: fast troubleshooting skill.

Challenge 9 — Second Highest Value

SELECT MAX(revenue)
FROM sales
WHERE revenue < (SELECT MAX(revenue) FROM sales);

Classic — but still asked!

Challenge 10 — Customer Retention Query

Customers who bought this month AND last month

SELECT this.customer_id
FROM sales this
JOIN sales last ON
  this.customer_id = last.customer_id AND
  DATE_TRUNC('month', this.date) = DATE_TRUNC('month', CURRENT_DATE)
  AND DATE_TRUNC('month', last.date) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month');

Tests: business understanding.

Challenge 11 — Find Surging Users

Users with 3 consecutive login days

SELECT DISTINCT user_id
FROM (
  SELECT
    user_id,
    login_date,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date) AS r1,
    DATEADD(day,
      -DENSE_RANK() OVER (PARTITION BY user_id ORDER BY login_date),
      login_date) AS grp
  FROM logins
)
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Tests: pattern recognition.

Challenge 12 — Join with Complex Data Cleaning

SELECT *
FROM employees e
JOIN payroll p
  ON LOWER(TRIM(e.email)) = LOWER(TRIM(p.email));

Tests: handling bad data, real production skill.

What Interviewers Really Evaluate

When answering SQL questions:

✔ Clarity of logic ✔ Understanding of windowing ✔ Handling dirty/real-world data ✔ Performance consideration (avoid full scans!) ✔ Ability to explain why — not just what

Strong SQL separates data engineers from script runners.

Part 2 Coming Soon…

Next article in the series:

Part 2 — Data Modeling & Warehouse Design Interview Questions (with schemas + real business scenarios)