Back to Notes

SQL Classic Interview Problems

The recurring problem shapes that show up across DataLemur, LeetCode SQL 50, and real fintech/product interviews (Razorpay, PhonePe, Juspay-style rounds). Each pattern below is worth being able to write cold, without reference, in under 5 minutes.

Second Highest Salary

-- DENSE_RANK — the preferred method, handles ties correctly
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) ranked WHERE rnk = 2;

-- Subquery alternative
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

The DENSE_RANK version is preferred because it correctly handles ties — if two employees share the highest salary, "second highest" should be the next distinct value, which DENSE_RANK gives directly; a naive LIMIT 1 OFFSET 1 approach would instead return a second row still at the tied-highest salary.

Top-N Per Group

SELECT department, name, salary FROM (
  SELECT department, name, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked WHERE rn <= 3;

See [[SQL Window Functions]] for the full ranking-function comparison this pattern relies on.

Consecutive Duplicates / Consecutive Values

-- Three consecutive identical values in a sequential id column
SELECT DISTINCT l1.num AS consecutive_nums
FROM logs l1, logs l2, logs l3
WHERE l1.id = l2.id - 1 AND l2.id = l3.id - 1
  AND l1.num = l2.num AND l2.num = l3.num;

Self-joining the same table three times, offset by id, is the standard trick for "N consecutive rows satisfying a condition" — the offsets encode adjacency without needing a procedural loop.

Active Users in a Window

SELECT COUNT(DISTINCT user_id) AS active_users
FROM activity
WHERE activity_date >= CURRENT_DATE - INTERVAL '30 days';

The Instacart-Style Case Study (a Common DataLemur Dataset Shape)

orders(order_id, user_id, order_dow, order_hour_of_day, days_since_prior_order)
order_products(order_id, product_id, add_to_cart_order, reordered)
products(product_id, product_name, aisle_id, department_id)

Reorder rate per product (a ratio pattern — count of a boolean condition over total count):

SELECT p.product_name,
  ROUND(100.0 * SUM(op.reordered) / COUNT(*), 2) AS reorder_rate
FROM order_products op
JOIN products p ON op.product_id = p.product_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(*) > 50          -- filter out low-volume products before ranking — avoids noisy small-sample rates
ORDER BY reorder_rate DESC
LIMIT 5;

Why HAVING COUNT(*) > 50 matters here, not just style: without a minimum-volume filter, a product ordered exactly once and reordered that one time shows a "100% reorder rate" — technically true, statistically meaningless. Filtering low-volume rows before ranking by a computed ratio is a recurring, important pattern any time a metric is a proportion (see [[SQL Aggregates & GROUP BY]]'s percentage-of-total pattern for the same family of query).

Most popular products per department: the exact top-N-per-group pattern above, partitioned by department_id instead of department string.

Customer churn signal: days_since_prior_order > 30 — a simple threshold filter, but often the seed of a much larger cohort-analysis question if the interviewer extends it.

The Concept Checklist (What These Problems Are Actually Testing)

  • JOINs — INNER/LEFT/self-join fluency (can you write the right join type without hesitating)
  • GROUP BY + HAVING vs WHERE execution order
  • CTEs, both plain and recursive
  • Window functions — ranking family, LAG/LEAD, running totals
  • Index/EXPLAIN literacy — can you reason about why a query is slow, not just write a correct one
  • ACID — the four-sentence verbal explanation, cold

Interview Drills

  1. Why is DENSE_RANK preferred over LIMIT 1 OFFSET 1 for "second highest salary"? — OFFSET 1 skips exactly one row, not one distinct value — if the top salary is tied across two employees, offsetting by 1 row still lands on a row at the tied-highest value, not the true second-highest distinct salary; DENSE_RANK explicitly ranks by distinct value, sidestepping this.
  2. Why filter HAVING COUNT(*) > 50 before ranking products by reorder rate? — A ratio computed from a tiny sample (one order, reordered once = "100%") is statistically meaningless noise that would otherwise dominate a "top 5 by reorder rate" ranking — filtering low-volume groups first ensures the ranked metric reflects a meaningful sample size.

Cross-links

[[SQL Window Functions]] · [[SQL Aggregates & GROUP BY]] · [[SQL JOINs]]