Loading…
Loading…
SQL tutorial · Expert
⏱️ 30 mins read
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).
-- 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-- Pattern 1: 2nd highest salary per department
WITH ranked AS (
SELECT name, salary, department_id,
DENSE_RANK() OVER
(PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT * FROM ranked WHERE rnk = 2;
-- Pattern 2: consecutive login streak >= 3 days
-- (logins arrived in Topic 25: Karl has a 3-day streak, Maike 4)
WITH numbered AS (
SELECT customer_id, login_date,
DATE_SUB(login_date, INTERVAL
ROW_NUMBER() OVER
(PARTITION BY customer_id ORDER BY login_date) DAY) AS grp
FROM logins
)
SELECT customer_id,
MIN(login_date) AS streak_start,
COUNT(*) AS streak
FROM numbered
GROUP BY customer_id, grp
HAVING COUNT(*) >= 3;
-- Pattern 3: Year-over-year revenue growth (needs 2+ years of data)
WITH annual AS (
SELECT YEAR(order_date) AS yr, SUM(total) AS revenue
FROM orders GROUP BY YEAR(order_date)
)
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 curr
LEFT JOIN annual prev ON curr.yr = prev.yr + 1;
-- ShopCo has one year, so prev is NULL — the pattern needs history
-- Pattern 4: keep only the latest record per customer
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER
(PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn = 1;| id | name | department_id | salary |
|---|---|---|---|
| 1 | Ada | 1 | 145000 |
| 2 | Grace | 1 | 115000 |
| 3 | Alan | 2 | 92000 |
| 4 | Edsger | NULL | 78000 |
| 5 | Barbara | 3 | 64000 |
| value |
|---|
| 1: Jan 1 |
| 2 |
| 3 + Mar 1 · 2: Feb 1 |
| 2 · 3: Apr 1 · 4: May 1 5: Mar 1 |
| 2 |
| 3 |
| 4 |
| id | customer_id | total | order_date |
|---|---|---|---|
| 1 | 1 | 240.00 | 2024-01-15 |
| 2 | 2 | 89.90 | 2024-02-03 |
| 3 | 1 | 430.00 | 2024-03-11 |
| 4 | 3 | 59.50 | 2024-04-02 |
| 5 | 4 | 120.00 | 2024-05-20 |
The most famous SQL interview question. On ShopCo the answer is 115000 (Grace). The LIMIT/OFFSET answer works here but breaks when two people tie at the top; the MAX-subquery answer breaks the same way; only DENSE_RANK handles ties the way the question implies. Saying WHICH edge case breaks each approach is the senior answer.
-- Way 1: breaks on ties at the top
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Way 2: same fragility, one line
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- Way 3: correct — DENSE_RANK skips nothing (Topic 18)
WITH ranked AS (
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT name, salary FROM ranked WHERE rnk = 2; -- Grace, 115000Consecutive dates share a property: subtracting their row position from the date yields the same constant. Run the trick on ShopCo's logins and you can SEE the groups form — Karl's Jan 1-3 collapses into one grp, his isolated Mar 1 gets its own, Maike's four days another.
-- customer_id | login_date | rn | grp = date - rn days
-- 1 | 2024-01-01 | 1 | 2023-12-31 ← same grp = one streak
-- 1 | 2024-01-02 | 2 | 2023-12-31 ←
-- 1 | 2024-01-03 | 3 | 2023-12-31 ←
-- 1 | 2024-03-01 | 4 | 2024-02-26 ← gap breaks the streak
-- 5 | 2024-03-01..04 → grp 2024-02-28 ×4 (Maike's 4-day streak)
-- GROUP BY customer_id, grp; HAVING COUNT(*) >= 3 → Karl(3), Maike(4)Every interview pattern in this topic runs on the ShopCo tables you've built all roadmap long. That is the final lesson: interview questions are not new SQL — they are Topics 1-24 recombined. Given any question, ask the five clarifying questions, then reach for the topic that owns the shape: 'top-N per group' → Topic 18, 'who never did X' → Topic 15, 'how many did X' → Topics 8-11.
-- Your drill: re-derive each classic answer from the seed data
-- 'Employees earning above average' → Topic 16
-- 'Departments with more than 1 person' → Topics 9-10
-- 'Customers who never ordered' → Topic 15 anti-join
-- 'Running revenue total' → Topic 18
-- 'Customers with a 3+ day streak' → this topic's trickJumping to complex CTEs immediately — start with the simplest query that works, then refactor. Not handling ties in RANK/ROW_NUMBER as the question requires. Forgetting to check for NULLs in edge cases. Not testing with sample data mentally.
Step 1: Clarify the question. Step 2: State your approach before writing. Step 3: Start simple, refactor to complex. Step 4: Check NULLs, ties, empty inputs. Step 5: Discuss performance and alternative approaches. Interviewers value communication as much as the final query.
1. The 2nd-highest salary on ShopCo is 115000 (Grace). Which approach survives salary ties at the top?
2. The consecutive-streak trick transforms 'consecutive' into…?
3. Running the streak query (HAVING COUNT(*) >= 3) on ShopCo's logins finds…?
4. RANK vs DENSE_RANK on salaries 145000, 145000, 115000 gives…?
5. 'Keep only the latest record per customer' is solved with…?
6. Year-over-year growth on ShopCo's orders returns NULL for prev_yr because…?
Return the name, department_id, and salary of the highest-paid employee in each department.
⚡ Solve it in the SQL playground →Constantly, but in structure: clarify, plan aloud, write, then self-review ('ties at the cutoff — DENSE_RANK instead of OFFSET'). Silence reads as being stuck. The strongest candidates narrate trade-offs, not keystrokes — and asking one clarifying question before typing is worth more than any syntax.
Say so, then reason from what you know: 'I haven't used LAG, but the shape I need is previous-row access, which a self-join on (customer_id, order_date) can also express...' Interviewers score reasoning under uncertainty far higher than memorized syntax, and pretending to know a function you can't use is the fastest fail.