Interview Question Patterns — Find Duplicates, Gaps, Islands

SELECT e.*
FROM employees e
WHERE salary IN (
  SELECT salary
  FROM employees
  GROUP BY salary
  HAVING COUNT(*) > 1
)
ORDER BY salary;

Find salaries that appear more than once, then return all employees with those salaries. Different from finding duplicates by ID — here duplicates are by value.

SELECT o1.order_id + 1 AS gap_start
FROM orders o1
WHERE NOT EXISTS (
  SELECT 1 FROM orders o2
  WHERE o2.order_id = o1.order_id + 1
)
AND o1.order_id < (SELECT MAX(order_id) FROM orders);

Find numbers where the next number doesn't exist. Useful for detecting missing invoices, skipped IDs, or gaps in sequences. A classic logic puzzle.

UPDATE employees
SET is_active = CASE is_active
  WHEN 1 THEN 0
  WHEN 0 THEN 1
END;

Toggle a boolean column. CASE WHEN in UPDATE statements works exactly like in SELECT. This is a classic one-liner interview question.

WITH ordered AS (
  SELECT name, hire_date,
         LAG(name)  OVER (ORDER BY hire_date) AS prev_hire,
         LEAD(name) OVER (ORDER BY hire_date) AS next_hire
  FROM employees
)
SELECT prev_hire, name, next_hire
FROM ordered
WHERE name = 'Rahul Sharma';

LAG and LEAD solve "previous/next in sequence" problems elegantly. Without window functions this would require a complex self-join.

-- MySQL:
SELECT department,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;

-- PostgreSQL:
SELECT department,
       STRING_AGG(name, ', ' ORDER BY name) AS employees
FROM employees
GROUP BY department;

GROUP_CONCAT / STRING_AGG aggregates strings. Useful for building comma-separated lists in reports. The ORDER BY inside keeps the list sorted.