SQL Interview Preparation

What You'll Learn

SQL interviews at top companies test: window functions (RANK, LAG, ROW_NUMBER), CTEs, self-joins, date math, deduplication, and business logic translation. The interview process: clarify requirements → think aloud → write simple → optimize → discuss edge cases (NULLs, ties, empty tables).

Syntax

-- Pattern 1: Top-N per group using ROW_NUMBER
-- Pattern 2: Consecutive events using date - ROW_NUMBER
-- Pattern 3: YoY growth using self-join or LAG
-- Pattern 4: Deduplication using ROW_NUMBER

Example

-- Pattern 1: 2nd highest salary per dept
WITH ranked AS (
  SELECT name, salary, department,
    DENSE_RANK() OVER
      (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk = 2;

-- Pattern 2: Consecutive login streak >= 3 days
WITH numbered AS (
  SELECT user_id, login_date,
    DATE_SUB(login_date, INTERVAL
      ROW_NUMBER() OVER
        (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM logins
)
SELECT user_id, COUNT(*) AS streak
FROM numbered GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

-- Pattern 3: Year-over-year revenue growth
SELECT
  curr.yr, curr.revenue, prev.revenue AS prev_yr,
  ROUND((curr.revenue-prev.revenue)*100.0
    /prev.revenue,1) AS yoy_pct
FROM annual_revenue curr
LEFT JOIN annual_revenue prev ON curr.yr = prev.yr + 1;

-- Pattern 4: Keep only latest record per user
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER
      (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM events
)
SELECT * FROM ranked WHERE rn = 1;

Common Mistakes

Interview Tips

Practice