LeetCode SQL Top 50 & Classic Problems
Complete collection of essential LeetCode SQL interview problems across Easy, Medium, and Hard with full solutions and explanations
LeetCode SQL Top 50 & Classic Interview Problems
This curated collection gathers the most frequently asked SQL interview questions from LeetCode, organized by difficulty and pattern. Try writing the SQL queries before expanding the solutions!
1. Easy Classics & Core Patterns
Problem:
Table Person has (personId, lastName, firstName).
Table Address has (addressId, personId, city, state).
Report firstName, lastName, city, and state for each person. If an address is not present, report NULL for city and state.
Solution:
SELECT
p.firstName,
p.lastName,
a.city,
a.state
FROM Person p
LEFT JOIN Address a ON p.personId = a.personId;Key Concept: Use LEFT JOIN to retain all rows from the primary table even if no corresponding record exists in the secondary table.
Problem:
Table Employee has (id, name, salary, managerId).
Find the employees who earn strictly more than their direct managers.
Solution:
SELECT e.name AS Employee
FROM Employee e
JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary;Key Concept: Self-join the Employee table as e (subordinate) and m (manager) on e.managerId = m.id.
Problem:
Table Person has (id, email).
Report all duplicate emails (emails occurring more than once).
Solution:
SELECT email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;Problem:
Table Customers has (id, name).
Table Orders has (id, customerId).
Find all customers who never placed any order.
Solution (Approach 1: LEFT JOIN Anti-join - Recommended):
SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.id IS NULL;Solution (Approach 2: NOT EXISTS):
SELECT c.name AS Customers
FROM Customers c
WHERE NOT EXISTS (
SELECT 1 FROM Orders o WHERE o.customerId = c.id
);Problem:
Table Weather has (id, recordDate, temperature).
Find all dates' id with higher temperatures compared to their previous dates (yesterday).
Solution:
-- PostgreSQL / SQLite syntax
SELECT w1.id
FROM Weather w1
JOIN Weather w2 ON JULIANDAY(w1.recordDate) = JULIANDAY(w2.recordDate) + 1
WHERE w1.temperature > w2.temperature;
-- MySQL syntax
-- JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1Pitfall: Never assume id values are consecutive or sorted by date! Always join using explicit date arithmetic.
Problem:
Table Customer has (id, name, referee_id).
Find the names of the customers that are either not referred by anyone or not referred by the customer with id = 2.
Solution:
SELECT name
FROM Customer
WHERE referee_id != 2 OR referee_id IS NULL;Pitfall: In SQL Three-Valued Logic, NULL != 2 evaluates to UNKNOWN, so rows with referee_id IS NULL will be silently dropped unless OR referee_id IS NULL (or COALESCE(referee_id, 0) != 2) is included!
Problem:
Table World has (name, continent, area, population, gdp).
A country is big if it has an area of at least 3,000,000 km² or a population of at least 25,000,000.
Solution:
SELECT name, population, area
FROM World
WHERE area >= 3000000 OR population >= 25000000;Problem:
Table Cinema has (id, movie, description, rating).
Report movies with an odd-numbered ID and a description that is not 'boring', ordered by rating descending.
Solution:
SELECT id, movie, description, rating
FROM Cinema
WHERE (id % 2 = 1) AND description <> 'boring'
ORDER BY rating DESC;Problem:
Table Salary has (id, name, sex, salary).
Swap all 'f' and 'm' values in a single UPDATE statement without using an intermediate temporary table.
Solution:
UPDATE Salary
SET sex = CASE
WHEN sex = 'm' THEN 'f'
ELSE 'm'
END;Problem:
Prices has (product_id, start_date, end_date, price).
UnitsSold has (product_id, purchase_date, units).
Find the average selling price for each product, rounded to 2 decimal places. If a product has no sales, report 0.
Solution:
SELECT
p.product_id,
COALESCE(ROUND(SUM(p.price * u.units) * 1.0 / NULLIF(SUM(u.units), 0), 2), 0) AS average_price
FROM Prices p
LEFT JOIN UnitsSold u ON p.product_id = u.product_id
AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;Problem:
Students(student_id, student_name)
Subjects(subject_name)
Examinations(student_id, subject_name)
Find the number of times each student attended each exam, including 0 times for unattended subjects.
Solution:
SELECT
s.student_id,
s.student_name,
sub.subject_name,
COUNT(e.student_id) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e ON s.student_id = e.student_id
AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;Trick: CROSS JOIN creates all student-subject combinations, and COUNT(e.student_id) counts matching exams (evaluating to 0 when NULL).
2. Medium Interview Questions & Analytical SQL
Problem:
Table Employee has (id, salary).
Find the second highest salary. If no second highest salary exists, return NULL.
Solution (Approach 1: Subquery with MAX - Fast & Clean):
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);Solution (Approach 2: Window Function CTE):
WITH RankedSalaries AS (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employee
)
SELECT (
SELECT salary
FROM RankedSalaries
WHERE rnk = 2
LIMIT 1
) AS SecondHighestSalary;Problem:
Write a SQL function to return the N-th highest salary from Employee. If fewer than N distinct salaries exist, return NULL.
Solution:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET N
);
END;Problem:
Table Scores has (id, score).
Rank the scores ordered descending. If there is a tie, both should share the same rank, and the next rank number should not be skipped.
Solution:
SELECT
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS 'rank'
FROM Scores;Problem:
Table Logs has (id, num).
Find all numbers that appear at least three times consecutively.
Solution (Using LEAD and LAG):
WITH CheckedLogs AS (
SELECT
num,
LAG(num, 1) OVER (ORDER BY id) AS prev_num,
LEAD(num, 1) OVER (ORDER BY id) AS next_num
FROM Logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM CheckedLogs
WHERE num = prev_num AND num = next_num;Problem:
Employee(id, name, salary, departmentId)
Department(id, name)
Find employees who have the highest salary in each department. Include all tied employees.
Solution:
WITH RankedDeptEmployees AS (
SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary,
DENSE_RANK() OVER (
PARTITION BY e.departmentId
ORDER BY e.salary DESC
) AS rnk
FROM Employee e
JOIN Department d ON e.departmentId = d.id
)
SELECT Department, Employee, Salary
FROM RankedDeptEmployees
WHERE rnk = 1;Problem:
Table Activity has (player_id, device_id, event_date, games_played).
Calculate the fraction of players that logged in again on the day immediately following the day they first logged in, rounded to 2 decimal places.
Solution:
WITH FirstLogins AS (
SELECT
player_id,
MIN(event_date) AS first_date
FROM Activity
GROUP BY player_id
)
SELECT
ROUND(COUNT(a.player_id) * 1.0 / (SELECT COUNT(*) FROM FirstLogins), 2) AS fraction
FROM FirstLogins f
JOIN Activity a ON f.player_id = a.player_id
AND JULIANDAY(a.event_date) = JULIANDAY(f.first_date) + 1;Problem:
Table Employee has (id, name, department, managerId).
Find managers with at least five direct reports.
Solution:
SELECT m.name
FROM Employee e
JOIN Employee m ON e.managerId = m.id
GROUP BY m.id, m.name
HAVING COUNT(e.id) >= 5;Problem:
Table Products has (product_id, new_price, change_date). Initial price of all products is 10.
Find the prices of all products on date '2019-08-16'.
Solution:
WITH LatestPrice AS (
SELECT
product_id,
new_price,
ROW_NUMBER() OVER (
PARTITION BY product_id
ORDER BY change_date DESC
) AS rn
FROM Products
WHERE change_date <= '2019-08-16'
),
AllProducts AS (
SELECT DISTINCT product_id FROM Products
)
SELECT
a.product_id,
COALESCE(l.new_price, 10) AS price
FROM AllProducts a
LEFT JOIN LatestPrice l ON a.product_id = l.product_id AND l.rn = 1;Problem:
Table Delivery has (delivery_id, customer_id, order_date, customer_pref_delivery_date).
If customer preferred delivery date matches order date, the order is immediate; otherwise scheduled.
Find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.
Solution:
WITH FirstOrders AS (
SELECT
customer_id,
order_date,
customer_pref_delivery_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date ASC
) AS rn
FROM Delivery
)
SELECT
ROUND(
SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) * 100.0 /
COUNT(*), 2
) AS immediate_percentage
FROM FirstOrders
WHERE rn = 1;Problem:
Table Queue has (person_id, person_name, weight, turn). Bus weight limit is 1000 kg.
Find the name of the last person that can fit into the bus without exceeding the weight limit.
Solution:
WITH RunningWeight AS (
SELECT
person_name,
weight,
turn,
SUM(weight) OVER (
ORDER BY turn ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS total_weight
FROM Queue
)
SELECT person_name
FROM RunningWeight
WHERE total_weight <= 1000
ORDER BY turn DESC
LIMIT 1;Problem:
Table Accounts has (account_id, income).
Calculate number of bank accounts for each salary category:
'Low Salary': income < $20,000'Average Salary': income in [$20,000, $50,000]'High Salary': income > $50,000
Result must include all three categories, even if a category has 0 accounts!
Solution:
SELECT 'Low Salary' AS category, COUNT(*) AS accounts_count
FROM Accounts WHERE income < 20000
UNION
SELECT 'Average Salary' AS category, COUNT(*) AS accounts_count
FROM Accounts WHERE income BETWEEN 20000 AND 50000
UNION
SELECT 'High Salary' AS category, COUNT(*) AS accounts_count
FROM Accounts WHERE income > 50000;Why UNION: Standard GROUP BY with CASE omits categories that have 0 matching records. UNION ensures all 3 rows exist in the output with a count of 0.
3. Hard LeetCode Problems & Complex Analytics
Problem:
A company wants to find employees who earn top 3 unique salaries in each department.
Employee(id, name, salary, departmentId), Department(id, name).
Solution:
WITH RankedSalaries AS (
SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary,
DENSE_RANK() OVER (
PARTITION BY e.departmentId
ORDER BY e.salary DESC
) AS rnk
FROM Employee e
JOIN Department d ON e.departmentId = d.id
)
SELECT Department, Employee, Salary
FROM RankedSalaries
WHERE rnk <= 3;Problem:
Trips(id, client_id, driver_id, city_id, status, request_at)
Users(users_id, banned, role)
Find cancellation rate of requests with unbanned users (both client AND driver must be unbanned) each day between '2013-10-01' and '2013-10-03'. Round to 2 decimal places.
Solution:
SELECT
t.request_at AS Day,
ROUND(
SUM(CASE WHEN t.status LIKE 'cancelled%' THEN 1.0 ELSE 0.0 END) /
COUNT(*), 2
) AS 'Cancellation Rate'
FROM Trips t
JOIN Users c ON t.client_id = c.users_id AND c.banned = 'No'
JOIN Users d ON t.driver_id = d.users_id AND d.banned = 'No'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at;Problem:
Table Stadium has (id, visit_date, people).
Display records with three or more consecutive rows where the number of people was greater than or equal to 100. Order by visit_date.
Solution:
WITH Filtered AS (
SELECT
id,
visit_date,
people,
id - ROW_NUMBER() OVER (ORDER BY id) AS grp
FROM Stadium
WHERE people >= 100
),
GroupCounts AS (
SELECT
*,
COUNT(*) OVER (PARTITION BY grp) AS group_size
FROM Filtered
)
SELECT id, visit_date, people
FROM GroupCounts
WHERE group_size >= 3
ORDER BY visit_date ASC;The Island Trick: For consecutive IDs where condition holds, id - ROW_NUMBER() OVER (ORDER BY id) produces an identical constant grp value! Grouping by grp clusters consecutive streaks of arbitrary length effortlessly.