LeetCode SQL Problems by Problem Domain
Useful Reference
- LC 180. Consecutive Numbers: Contains a detailed tutorial on gaps and islands problems and how to effectively use
ROW_NUMBER()to identify where gaps occur. - LC 185. Department Top Three Salaries: Contains a detailed tutorial on correlated subqueries.
Summer 2021
Best problems
- LC 586. Customer Placing the Largest Number of Orders
- LC 601. Human Traffic of Stadium
- LC 608. Tree Node
- LC 615. Average Salary: Departments VS Company
- LC 618. Students Report By Geography
- LC 1045. Customers Who Bought All Products
- LC 1126. Active Businesses
- LC 1127. User Purchase Platform
- LC 1159. Market Analysis II
- LC 1179. Reformat Department Table
- LC 1212. Team Scores in Football Tournament
- LC 1225. Report Contiguous Dates
- LC 1321. Restaurant Growth
- LC 1341. Movie Rating
- LC 1369. Get the Second Most Recent Activity
- LC 1384. Total Sales Amount by Year
- LC 1454. Active Users
- LC 1468. Calculate Salaries
- LC 1596. The Most Frequently Ordered Products for Each Customer
- LC 1613. Find the Missing IDs
- LC 1811. Find Interview Candidates
Good problems
- LC 607. Sales Person
- LC 626. Exchange Seats
- LC 1141. User Activity for the Past 30 Days I
- LC 1193. Monthly Transactions I
- LC 1205. Monthly Transactions II
- LC 1285. Find the Start and End Number of Continuous Ranges
- LC 1501. Countries You Can Safely Invest In
- LC 1699. Number of Calls Between Two Persons
- LC 1709. Biggest Window Between Visits
- LC 1747. Leetflex Banned Accounts
Revisit
- LC 607. Sales Person
- LC 627. Swap Salary
- LC 1045. Customers Who Bought All Products
- LC 1050. Actors and Directors Who Cooperated At Least Three Times
- LC 1076. Project Employees II
- LC 1083. Sales Analysis II
- LC 1084. Sales Analysis III
- LC 1098. Unpopular Books
- LC 1127. User Purchase Platform
- LC 1179. Reformat Department Table
- LC 1205. Monthly Transactions II
- LC 1212. Team Scores in Football Tournament
- LC 1225. Report Contiguous Dates
- LC 1241. Number of Comments per Post
- LC 1280. Students and Examinations
- LC 1336. Number of Transactions per Visit
- LC 1364. Number of Trusted Contacts of a Customer
- LC 1384. Total Sales Amount by Year
- LC 1501. Countries You Can Safely Invest In
- LC 1511. Customer Order Frequency
- LC 1699. Number of Calls Between Two Persons
- LC 1709. Biggest Window Between Visits
- LC 1783. Grand Slam Titles
- LC 1811. Find Interview Candidates
Beginner problems
- LC 175. Combine Two Tables
- LC 182. Duplicate Emails
- LC 511. Game Play Analysis I
- LC 577. Employee Bonus
- LC 580. Count Student Number in Departments
- LC 584. Find Customer Referee
- LC 595. Big Countries
- LC 620. Not Boring Movies
- LC 1068. Product Sales Analysis I
- LC 1069. Product Sales Analysis II
- LC 1075. Project Employees I
- LC 1113. Reported Posts
- LC 1148. Article Views I
- LC 1294. Weather Type in Each Country
- LC 1327. List the Products Ordered in a Period
- LC 1350. Students With Invalid Departments
- LC 1378. Replace Employee ID With The Unique Identifier
- LC 1407. Top Travellers
- LC 1587. Bank Account Summary II
- LC 1683. Invalid Tweets
- LC 1693. Daily Leads and Partners
- LC 1757. Recyclable and Low Fat Products
- LC 1821. Find Customers With Positive Revenue this Year
CASE statements
Correlated subquery
- LC 176. Second Highest Salary
- LC 178. Rank Scores
- LC 185. Department Top Three Salaries
- LC 570. Managers with at Least 5 Direct Reports
- LC 579. Find Cumulative Salary of an Employee
- LC 585. Investments in 2016
Dates and timestamps
- LC 197. Rising Temperature
- LC 1084. Sales Analysis III
- LC 1193. Monthly Transactions I
- LC 1384. Total Sales Amount by Year
- LC 1454. Active Users
- LC 1543. Fix Product Name Format
DELETE
DISTINCT
EXISTS and NOT EXISTS
- LC 183. Customers Who Never Order
- LC 262. Trips and Users
- LC 585. Investments in 2016
- LC 608. Tree Node
- LC 1098. Unpopular Books
- LC 1264. Page Recommendations
- LC 1892. Page Recommendations II
Gaps and islands
- LC 180. Consecutive Numbers
- LC 601. Human Traffic of Stadium
- LC 603. Consecutive Available Seats
- LC 1225. Report Contiguous Dates
- LC 1285. Find the Start and End Number of Continuous Ranges
- LC 1454. Active Users
IN and NOT IN
- LC 183. Customers Who Never Order
- LC 184. Department Highest Salary
- LC 607. Sales Person
- LC 608. Tree Node
- LC 1098. Unpopular Books
- LC 1264. Page Recommendations
- LC 1607. Sellers With No Sales
- LC 1613. Find the Missing IDs
Indicator variables
- LC 262. Trips and Users
- LC 578. Get Highest Answer Rate Question
- LC 1174. Immediate Food Delivery II
- LC 1322. Ads Performance
- LC 1398. Customers Who Bought Products A and B but Not C
- LC 1811. Find Interview Candidates
- LC 1907. Count Salary Categories
Joins
Cross-joins
- LC 196. Delete Duplicate Emails
- LC 579. Find Cumulative Salary of an Employee
- LC 612. Shortest Distance in a Plane
- LC 613. Shortest Distance in a Line
- LC 1127. User Purchase Platform
- LC 1280. Students and Examinations
- LC 1892. Page Recommendations II
LEFT JOIN
Non-equi-joins
- LC 178. Rank Scores
- LC 196. Delete Duplicate Emails
- LC 197. Rising Temperature
- LC 569. Median Employee Salary
- LC 1097. Game Play Analysis V
- LC 1212. Team Scores in Football Tournament
- LC 1251. Average Selling Price
- LC 1384. Total Sales Amount by Year
- LC 1555. Bank Account Summary
- LC 1623. All Valid Triplets That Can Represent a Country
- LC 1747. Leetflex Banned Accounts
- LC 1811. Find Interview Candidates
RIGHT JOIN
Self-joins
- LC 181. Employees Earning More Than Their Managers
- LC 196. Delete Duplicate Emails
- LC 197. Rising Temperature
- LC 1241. Number of Comments per Post
- LC 1251. Average Selling Price
- LC 1364. Number of Trusted Contacts of a Customer
- LC 1747. Leetflex Banned Accounts
Median
NOT EXISTS (see EXISTS)
NOT IN (see IN)
NULL values and effective management
- LC 176. Second Highest Salary
- LC 584. Find Customer Referee
- LC 608. Tree Node
- LC 1098. Unpopular Books
Pivoting
Result set to multiple rows
- LC 618. Students Report By Geography
- LC 1179. Reformat Department Table
- LC 1777. Product''s Price for Each Store
Recursive CTEs
- LC 571. Find Median Given Frequency of Numbers
- LC 579. Find Cumulative Salary of an Employee
- LC 1270. All People Report to the Given Manager
- LC 1336. Number of Transactions per Visit
- LC 1384. Total Sales Amount by Year
- LC 1613. Find the Missing IDs
- LC 1635. Hopper Company Queries I
- LC 1645. Hopper Company Queries II
- LC 1651. Hopper Company Queries III
- LC 1767. Find the Subtasks That Did Not Execute
Regular expressions
Stored procedures
UNION and UNION ALL
UPDATE
Window functions
Named windows
- LC 1084. Sales Analysis III
- LC 1321. Restaurant Growth
- LC 1341. Movie Rating
- LC 1369. Get the Second Most Recent Activity
- LC 1468. Calculate Salaries
AVG()
- LC 615. Average Salary: Departments VS Company
- LC 1126. Active Businesses
- LC 1321. Restaurant Growth
COUNT()
- LC 601. Human Traffic of Stadium
- LC 603. Consecutive Available Seats
- LC 1050. Actors and Directors Who Cooperated At Least Three Times
- LC 1303. Find the Team Size
- LC 1341. Movie Rating
- LC 1369. Get the Second Most Recent Activity
DENSE_RANK()
- LC 178. Rank Scores
- LC 184. Department Highest Salary
- LC 185. Department Top Three Salaries
- LC 1341. Movie Rating
LAG()
LEAD()
MAX()
- LC 1084. Sales Analysis III
- LC 1126. Active Businesses
- LC 1412. Find the Quiet Students in All Exams
- LC 1468. Calculate Salaries
- LC 1596. The Most Frequently Ordered Products for Each Customer
- LC 1867. Orders With Maximum Quantity Above Average
MIN()
PERCENTILE_CONT()
RANK()
- LC 512. Game Play Analysis II
- LC 586. Customer Placing the Largest Number of Orders
- LC 602. Friend Requests II: Who Has the Most Friends
- LC 1070. Product Sales Analysis III
- LC 1076. Project Employees II
- LC 1077. Project Employees III
- LC 1082. Sales Analysis I
- LC 1112. Highest Grade For Each Student
- LC 1164. Product Price at a Given Date
- LC 1174. Immediate Food Delivery II
- LC 1194. Tournament Winners
- LC 1341. Movie Rating
- LC 1355. Activity Participants
- LC 1369. Get the Second Most Recent Activity
- LC 1549. The Most Recent Orders for Each Product
- LC 1596. The Most Frequently Ordered Products for Each Customer
- LC 1831. Maximum Transaction Each Day
ROW_NUMBER()
- LC 180. Consecutive Numbers
- LC 569. Median Employee Salary
- LC 571. Find Median Given Frequency of Numbers
- LC 601. Human Traffic of Stadium
- LC 603. Consecutive Available Seats
- LC 618. Students Report By Geography
- LC 1285. Find the Start and End Number of Continuous Ranges
- LC 1454. Active Users
- LC 1532. The Most Recent Three Orders
SUM()
- LC 534. Game Play Analysis III
- LC 579. Find Cumulative Salary of an Employee
- LC 1204. Last Person to Fit in the Bus
- LC 1308. Running Total for Different Genders
- LC 1321. Restaurant Growth