Top 20 LeetCode SQL problems Asked In Top MNCs:-
Want to ace your next data or business analyst interview?
Here’s a list of the top 20 LeetCode SQL problems that are frequently asked by top companies like Google, Facebook, and Amazon. Master these SQL queries and be prepared to tackle anything from joining tables to calculating complex rankings.
Practice solving these problems to enhance your SQL skills and boost your chances of landing your dream job.
Check out the problems and start practicing today!
1. Combine Two Tables
LeetCode 175: https://leetcode.com/problems/combine-two-tables/
Problem :
Write a SQL query to combine two tables `Person` and `Address`, returning all people and their respective addresses (if they exist).
Normal Approach :
Use `LEFT JOIN` to merge the tables and return records even if the person doesn’t have an address.
Optimized Approach :
Make use of the `COALESCE()` function to handle null values in the address data.
2. Second Highest Salary
LeetCode 176: https://leetcode.com/problems/second-highest-salary/
Problem:
Write a SQL query to get the second highest salary from the `Employee` table.
Normal Approach :
Use `ORDER BY` with `LIMIT 1 OFFSET 1` to fetch the second-highest salary.
Optimized Approach :
Use a `DISTINCT` subquery to ensure duplicate salaries are ignored and employ a conditional statement to handle cases where there is no second-highest salary.
3. Nth Highest Salary
LeetCode 177: https://leetcode.com/problems/nth-highest-salary/
Problem:
Write a SQL query to get the Nth highest salary from the `Employee` table.
Normal Approach :
Use `ORDER BY` with `LIMIT` and `OFFSET` to fetch the Nth highest salary.
Optimized Approach :
Utilize a window function like `ROW_NUMBER()` or `DENSE_RANK()` for a more efficient solution.
4. Rank Scores
LeetCode 178: https://leetcode.com/problems/rank-scores/
Problem :
Write a SQL query to rank the scores from highest to lowest.
Normal Approach :
Use `ORDER BY` and create a ranking manually by incrementing the rank value for each row.
Optimized Approach :
Leverage the `RANK()` or `DENSE_RANK()` window function to efficiently handle ranking and deal with ties.
5. Consecutive Numbers
LeetCode 180: https://leetcode.com/problems/consecutive-numbers/
Problem :
Write a SQL query to find all numbers that appear consecutively at least three times.
Normal Approach :
Use self-joins to compare rows with their neighbors to identify consecutive entries.
Optimized Approach :
Employ the `LEAD()` or `LAG()` window functions to compare consecutive rows efficiently.
6. Employees Earning More Than Their Managers
LeetCode 181: https://leetcode.com/problems/employees-earning-more-than-their-managers/
Problem :
Write a SQL query to find employees who earn more than their managers.
Normal Approach :
Use self-joins on the `Employee` table to compare employee salaries with their manager’s salaries.
Optimized Approach :
Implement a correlated subquery to reduce redundant data scanning and make the query more efficient.
7. Duplicate Emails
LeetCode 182: https://leetcode.com/problems/duplicate-emails/
Problem :
Write a SQL query to find all duplicate email addresses in the `Person` table.
Normal Approach :
Use `GROUP BY` and `HAVING` to identify emails appearing more than once.
Optimized Approach :
Ensure performance optimization by using indexes on the email column for faster querying.
8. Customers Who Never Order
LeetCode 183: https://leetcode.com/problems/customers-who-never-order/
Problem :
Write a SQL query to find customers who never placed an order.
Normal Approach :
Use `LEFT JOIN` with the `Orders` table and filter out the customers with no matching orders.
Optimized Approach :
Utilize the `NOT EXISTS` clause for better query performance in large datasets.
9. Department Highest Salary
LeetCode 184: https://leetcode.com/problems/department-highest-salary/
Problem :
Write a SQL query to find the employee with the highest salary in each department.
Normal Approach :
Use `GROUP BY` on the department and fetch the highest salary using `MAX()`.
Optimized Approach :
Implement a window function like `RANK()` or `DENSE_RANK()` to get the highest salary for each department efficiently.
10. Department Top Three Salaries
LeetCode 185: https://leetcode.com/problems/department-top-three-salaries/
Problem :
Write a SQL query to find the top three salaries for each department.
Normal Approach :
Use `GROUP BY` with a subquery to fetch the top three salaries for each department.
Optimized Approach :
Use `ROW_NUMBER()` or `RANK()` window functions to rank salaries within departments and filter the top three.
11. Delete Duplicate Emails
LeetCode 196: https://leetcode.com/problems/delete-duplicate-emails/
Problem:
Write a SQL query to delete all duplicate email entries, leaving only unique records.
Normal Approach :
Use `GROUP BY` with `DELETE` to remove duplicate entries while keeping the first occurrence.
Optimized Approach :
Use a `ROW_NUMBER()` or `RANK()` function in a CTE to identify duplicates and delete accordingly for faster performance.
12. Rising Temperature
LeetCode 197: https://leetcode.com/problems/rising-temperature/)
Problem :
Write a SQL query to find all dates where the temperature is higher than the previous day.
Normal Approach :
Use self-join on the `Weather` table to compare temperatures between consecutive days.
Optimized Approach :
Use the `LAG()` window function to efficiently compare the temperature of the current day with the previous day’s temperature.
13. Trips and Users
LeetCode 262: https://leetcode.com/problems/trips-and-users/
Problem :
Write a SQL query to find all rides that were either cancelled by the client or driver.
Normal Approach :
Use `INNER JOIN` between `Trips` and `Users` tables, and apply filtering conditions to find cancelled rides.
Optimized Approach :
Leverage conditional aggregation to streamline the query performance on large datasets.
14. Game Play Analysis I
LeetCode 511: https://leetcode.com/problems/game-play-analysis-i/
Problem :
Write a SQL query to find the first login date for each player.
Normal Approach :
Use `GROUP BY` and `MIN()` to retrieve the first login date for each player.
Optimized Approach :
Utilize window functions like `ROW_NUMBER()` to efficiently fetch the earliest login without needing a group operation .
15. Game Play Analysis II
LeetCode 512: https://leetcode.com/problems/game-play-analysis-ii/
Problem :
Write a SQL query to find the number of players who logged in for consecutive days.
Normal Approach :
Use self-joins to compare login dates and identify consecutive logins.
Optimized Approach :
Employ the `LAG()` or `LEAD()` window functions to compare consecutive login dates more efficiently.
16. Game Play Analysis III
LeetCode 534: https://leetcode.com/problems/game-play-analysis-iii/
Problem :
Write a SQL query to find the first login for each player after they have played a game for three or more consecutive days.
Normal Approach :
Use self-joins and date arithmetic to calculate consecutive login streaks.
Optimized Approach :
Apply the `ROW_NUMBER()` function partitioned by the player and order by date to track streaks and filter by conditions.
17. Game Play Analysis IV
LeetCode 550: https://leetcode.com/problems/game-play-analysis-iv/
Problem :
Write a SQL query to find all players who played on consecutive days.
Normal Approach :
Use `GROUP BY` and self-joins to determine streaks of consecutive gameplay.
Optimized Approach :
Leverage the `LAG()` function to directly compare the login date to the previous day’s activity for each player.
18. Median Employee Salary
LeetCode 569: https://leetcode.com/problems/median-employee-salary/
Problem :
Write a SQL query to calculate the median salary for each department.
Normal Approach :
Use `GROUP BY` and calculate median manually by sorting and applying `LIMIT`/`OFFSET`.
Optimized Approach :
Use window functions like `PERCENTILE_CONT()` for efficient computation of median across departments.
19. Winning Candidate
LeetCode 574: https://leetcode.com/problems/winning-candidate/
Problem :
Write a SQL query to find the candidate with the most votes in an election.
Normal Approach :
Use `GROUP BY` with `COUNT()` to find the total votes for each candidate and select the one with the highest count.
Optimized Approach :
Apply the `RANK()` function to efficiently rank candidates based on votes and retrieve the top-ranked one.
20. Employee Bonus
LeetCode 577: https://leetcode.com/problems/employee-bonus/)
Problem :
Write a SQL query to find employees who receive bonuses based on the `Employee` and `Bonus` tables.
Normal Approach :
Use `INNER JOIN` to match employees with bonuses, then return those who qualify.
Optimized Approach :
Use `LEFT JOIN` to ensure all employees are included, and use a conditional filter to determine who has bonuses.
