Interview Patterns
18 min
SQL interview questions rarely test obscure syntax — they test whether you reach for the right pattern under time pressure. A few show up constantly enough to be worth recognizing on sight, combining tools you already have from this course.
"Nth highest value" — sort, then skip:
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- skip the top 1, take the next 1 = 2nd highest
LIMIT n OFFSET m means "skip the first m rows of the (ordered)
result, then take the next n." For the 3rd-highest, you'd use OFFSET 2, and so on.
'Group, aggregate, sort, take the top one' is the single most common interview pattern — it answers almost any 'which X has the most/highest/fewest Y' question.
A couple more patterns worth having ready:
"Find duplicates" — group by the value, keep groups with count > 1:
SELECT department FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
"Rows in A but not in B" — a LEFT JOIN where the right side didn't
match:
SELECT employees.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL; -- no matching department row
Under real interview time pressure, these patterns are worth recognizing by shape ("this is a top-N question," "this is a duplicates question") rather than re-deriving the SQL from scratch each time.
Classic interview question: write a query that returns the `name` and `salary` of the employee with the SECOND-highest salary company-wide. Return just that one row.
Write a query that returns the single `department` with the highest total payroll (sum of salaries), along with that total as `total_payroll`. Return just that one row.
Why is `SELECT ... ORDER BY salary DESC LIMIT 1 OFFSET 1` a more robust way to find the 'second-highest salary' than `MAX(salary) WHERE salary != (SELECT MAX(salary) ...)`?
You recognize the top-N (ORDER BY + LIMIT/OFFSET) and group-and-aggregate-then-sort patterns that cover a large fraction of real SQL interview questions.