This project solves the entire module of "LeetCode SQL 50" encompassing solutions written in three most famous structured query languages (Oracle SQL, PostgreSQL & MySQL). Additionally, I have also included Pandas solutions, offering a Python-based alternative for data manipulation and analysis.
The repository aims to provide intuitive and easy-to-understand answers to 50 LeetCode SQL problems that mimic real-world analytical scenarios.
This repository is structured as follows:
- Each SQL solution is stored in its own file named according to the LeetCode problem title.
- Each file will contain both (a) the problem statement and (b) the SQL Code at the buttom of the script.
- The solutions are organized into folders based on their corresponding segmentation within the LeetCode website.
- There are eight segments in total:
- Leetcode SQL 50 Part 1: SELECT Problem Sets
- Leetcode SQL 50 Part 2: JOINS Problem Sets
- Leetcode SQL 50 Part 3: Aggregate Functions Problem Sets
- Leetcode SQL 50 Part 4: Sorting and Grouping
- Leetcode SQL 50 Part 5: Advanced Select and Joins
- Leetcode SQL 50 Part 6: Subqueries
- Leetcode SQL 50 Part 7: Advanced String Functions / Regex / Clause
- Solutions in Pandas
I have utilized on a range of SQL syntax to answer all the 50 LeetCode problems, each questions more daunting than the last. I have covered the most basic syntax to more advance ones such as but not really limited to:
- Subquery, Aliasing, Common Table Expression (CTE)
- Different Types of JOIN: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, as well as self join
- String Functions
- Date Functions
- Conversion Functions
- Aggregation Functions
- Window Functions
- Conditional Expressions: WHERE clause, HAVING clause, CASE WHEN
- etc.
To supplement the SQL-based solutions, I have also written Pandas equivalents for all the SQL 50 problems. The Pandas solutions demonstrate:
- How to approach the same problem using Python and DataFrames.
- How SQL logic translates into Pandas chaining and transformations.
Folder: Solutions in Pandas
For SQL: Here's a list of the problems included in this repository:
- LeetCode #1757. Recyclable and Low Fat Products
- LeetCode #584. Find Customer Referee
- LeetCode #595. Big Countries
- LeetCode #1148. Article Views I
- LeetCode #1683. Invalid Tweets
- LeetCode #1378. Replace Employee ID With The Unique Identifier
- LeetCode #1068. Product Sales Analysis I
- LeetCode #1581. Customer Who Visited but Did Not Make Any Transactions
- LeetCode #197. Rising Temperature
- LeetCode #1661. Average Time of Process per Machine
- LeetCode #577. Employee Bonus
- LeetCode #1280. Students and Examinations
- LeetCode #570. Managers with at Least 5 Direct Reports
- LeetCode #1934. Confirmation Rate
- LeetCode #620. Not Boring Movies
- LeetCode #1251. Average Selling Price
- LeetCode #1075. Project Employees I
- LeetCode #1633. Percentage of Users Attended a Contest
- LeetCode #1211. Queries Quality and Percentage
- LeetCode #1193. Monthly Transactions I
- LeetCode #1174. Immediate Food Delivery II
- LeetCode #550. Game Play Analysis IV
- LeetCode #2356. Number of Unique Subjects Taught by Each Teacher
- LeetCode #1141. User Activity for the Past 30 Days I
- LeetCode #1070. Product Sales Analysis III
- LeetCode #596. Classes More Than 5 Students
- LeetCode #1729. Find Followers Count
- LeetCode #619. Biggest Single Number
- LeetCode #1045. Customers Who Bought All Products
- LeetCode #1731. The Number of Employees Which Report to Each Employee
- LeetCode #1789. Primary Department for Each Employee
- LeetCode #610. Triangle Judgement
- LeetCode #180. Consecutive Numbers
- LeetCode #1164. Product Price at a Given Date
- LeetCode #1204. Last Person to Fit in the Bus
- LeetCode #1907. Count Salary Categories
- LeetCode #11978. Employees Whose Manager Left the Company
- LeetCode #626. Exchange Seats
- LeetCode #1341. Movie Rating
- LeetCode #1321. Restaurant Growth
- LeetCode #602. Friend Requests II: Who Has the Most Friends
- LeetCode #585. Investments in 2016
- LeetCode #185. Department Top Three Salaries
- LeetCode #1667. Fix Names in a Table
- LeetCode #1527. Patients With a Condition
- LeetCode #196. Delete Duplicate Emails
- LeetCode #176. Second Highest Salary
- LeetCode #1484. Group Sold Products By The Date
- LeetCode #1327. List the Products Ordered in a Period
- LeetCode #1517. Find Users With Valid E-Mails
You can clone this repo and use these SQL solutions for reference. You'll notice what syntax I'm a fan of and a pattern on how I usually structure my queries, but there are many different ways to tackle one problem, so feel free to explore and draft a completely different SQL query structure to your liking.
Happy Coding! 🚀