Skip to content

About

No description, website, or topics provided.

Resources

Stars

2 stars

Watchers

1 watching

Forks

Latest commit

 

History

14 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

LeetCode SQL 50

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.

Repository Structure

This repository is structured as follows:

  1. Each SQL solution is stored in its own file named according to the LeetCode problem title.
  2. Each file will contain both (a) the problem statement and (b) the SQL Code at the buttom of the script.
  3. The solutions are organized into folders based on their corresponding segmentation within the LeetCode website.
  4. 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

Contents

Scope

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.

📌 Additions: Pandas Implementations

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:

Part 1: SELECT Problem Sets (Problems 1 - 5)

  1. LeetCode #1757. Recyclable and Low Fat Products
  2. LeetCode #584. Find Customer Referee
  3. LeetCode #595. Big Countries
  4. LeetCode #1148. Article Views I
  5. LeetCode #1683. Invalid Tweets

Part 2: JOINS Problem Sets (Problems 6 - 14)

  1. LeetCode #1378. Replace Employee ID With The Unique Identifier
  2. LeetCode #1068. Product Sales Analysis I
  3. LeetCode #1581. Customer Who Visited but Did Not Make Any Transactions
  4. LeetCode #197. Rising Temperature
  5. LeetCode #1661. Average Time of Process per Machine
  6. LeetCode #577. Employee Bonus
  7. LeetCode #1280. Students and Examinations
  8. LeetCode #570. Managers with at Least 5 Direct Reports
  9. LeetCode #1934. Confirmation Rate

Part 3: Aggregate Functions Problem Sets (Problems 15 - 22)

  1. LeetCode #620. Not Boring Movies
  2. LeetCode #1251. Average Selling Price
  3. LeetCode #1075. Project Employees I
  4. LeetCode #1633. Percentage of Users Attended a Contest
  5. LeetCode #1211. Queries Quality and Percentage
  6. LeetCode #1193. Monthly Transactions I
  7. LeetCode #1174. Immediate Food Delivery II
  8. LeetCode #550. Game Play Analysis IV

Part 4: Sorting and Grouping (Problems 23 - 29)

  1. LeetCode #2356. Number of Unique Subjects Taught by Each Teacher
  2. LeetCode #1141. User Activity for the Past 30 Days I
  3. LeetCode #1070. Product Sales Analysis III
  4. LeetCode #596. Classes More Than 5 Students
  5. LeetCode #1729. Find Followers Count
  6. LeetCode #619. Biggest Single Number
  7. LeetCode #1045. Customers Who Bought All Products

Part 5: Advanced Select and Joins (Problems 30 - 36)

  1. LeetCode #1731. The Number of Employees Which Report to Each Employee
  2. LeetCode #1789. Primary Department for Each Employee
  3. LeetCode #610. Triangle Judgement
  4. LeetCode #180. Consecutive Numbers
  5. LeetCode #1164. Product Price at a Given Date
  6. LeetCode #1204. Last Person to Fit in the Bus
  7. LeetCode #1907. Count Salary Categories

Part 6: Subqueries (Problems 37 - 43)

  1. LeetCode #11978. Employees Whose Manager Left the Company
  2. LeetCode #626. Exchange Seats
  3. LeetCode #1341. Movie Rating
  4. LeetCode #1321. Restaurant Growth
  5. LeetCode #602. Friend Requests II: Who Has the Most Friends
  6. LeetCode #585. Investments in 2016
  7. LeetCode #185. Department Top Three Salaries

Part 7: Advanced String Functions / Regex / Clause (Problems 44 - 50)

  1. LeetCode #1667. Fix Names in a Table
  2. LeetCode #1527. Patients With a Condition
  3. LeetCode #196. Delete Duplicate Emails
  4. LeetCode #176. Second Highest Salary
  5. LeetCode #1484. Group Sold Products By The Date
  6. LeetCode #1327. List the Products Ordered in a Period
  7. LeetCode #1517. Find Users With Valid E-Mails

Usage

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! 🚀

About

No description, website, or topics provided.

Resources

Stars

2 stars

Watchers

1 watching

Forks

Releases

Packages

Contributors

Languages