LeetCode SQL Problems: 249 Questions With Solutions

SELECT name, population, area
FROM   World
WHERE  area >= 3000000 OR population >= 25000000
ORDER  BY population DESC
LIMIT  1 OFFSET 1;   -- skip one, take one: the second row, not the top two

SELECT   customer_id, COUNT(*) AS orders
FROM     Orders
WHERE    status = 'paid'      -- filters ROWS, before grouping
GROUP BY customer_id
HAVING   COUNT(*) > 1         -- filters GROUPS, after
ORDER BY orders DESC
LIMIT    1;

SELECT e.name AS employee, m.name AS manager
FROM   Employee AS e
JOIN   Employee AS m ON m.id = e.managerId   -- one table, two roles
WHERE  e.salary > m.salary;

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         DENSE_RANK() OVER (PARTITION BY department
                            ORDER BY salary DESC) AS place
  FROM   Employee
) AS ranked
WHERE  place <= 3;   -- filtered outside: WHERE is evaluated before the window

SELECT e.name, e.salary
FROM   Employee AS e
WHERE  e.salary = (SELECT MAX(x.salary)
                   FROM   Employee AS x
                   WHERE  x.department = e.department);   -- re-runs per outer row

SELECT today.id
FROM   Weather AS yesterday
JOIN   Weather AS today
  ON   today.recordDate = yesterday.recordDate + INTERVAL 1 DAY
WHERE  today.temperature > yesterday.temperature;

-- a comparison never matches NULL, so those rows must be asked for
SELECT name FROM Customer WHERE referee_id <> 2 OR referee_id IS NULL;

-- an anti-join: keep only the rows the LEFT JOIN found no partner for
SELECT c.name
FROM   Customers AS c
LEFT JOIN Orders AS o ON o.customerId = c.id
WHERE  o.id IS NULL;

SELECT   student,
         MAX(CASE WHEN subject = 'Maths'   THEN score END) AS maths,    -- no ELSE
         MAX(CASE WHEN subject = 'Physics' THEN score END) AS physics,
         ROUND(SUM(CASE WHEN score < 50 THEN 1 ELSE 0 END)
               / COUNT(*), 2)                                AS fail_rate
FROM     Marks
GROUP BY student;