---

Not just solutions — a full study companion.

Every problem has the schema, sample data, a working query, a step-by-step breakdown, and a personal takeaway so you know why it works — not just that it does.

---

📌 Why This Repo?

Most SQL solution repos dump queries in a .sql file and call it done.

This one is different. Every single problem is its own documented file — with the table schema, sample input/output, the SQL solution, a numbered approach walking through the logic, the concepts it exercises, and a personal takeaway distilled from actually solving it. Think of it as the notes you wish someone had written before you started.

Who is this for?

• 🎓 Students preparing for placements / internships
• 💼 Anyone brushing up SQL for data analyst / data engineering roles
• 🏗️ People who want to understand why a query works, not just copy it

---

🗂️ Repo Structure

leetcode-sql-50/
│
├── 📂 easy/ # 32 problems — WHERE, JOINs, Aggregation, String Functions
├── 📂 medium/ # 17 problems — Subqueries, Window Functions, CTEs, UNION
├── 📂 hard/ # 1 problem — DENSE_RANK, Advanced Window Functions
└── 📄 README.md

Each file follows this exact structure:

# 🟡 [Problem Number] · [Title]
Difficulty + Topic badge | LeetCode link
---
📋 Problem Statement
🗂️ Schema (CREATE TABLE block)
📥 Sample Input (markdown table)
📤 Sample Output (markdown table)
💡 Solution (SQL code block)
🧠 Approach (numbered steps)
📌 Concepts Used (`backtick` tags)
💭 My Takeaway (1–2 sentences in plain language)

---

🟢 Easy — 32 Problems

#	LC #	Problem	Topic
01	1757	Recyclable and Low Fat Products	Filtering / WHERE
02	584	Find Customer Referee	NULL Handling / Filtering
03	595	Big Countries	Filtering / WHERE
04	1148	Article Views I	Filtering / WHERE
05	1683	Invalid Tweets	String Functions / LENGTH()
06	1378	Replace Employee ID With The Unique Identifier	JOIN / LEFT JOIN
07	1068	Product Sales Analysis I	JOIN / INNER JOIN
08	1581	Customer Who Visited but Did Not Make Any Transactions	LEFT JOIN / NULL / GROUP BY
09	197	Rising Temperature	Self JOIN / DATEDIFF
10	1661	Average Time of Process per Machine	Self JOIN / Aggregation
11	577	Employee Bonus	LEFT JOIN / NULL Handling
12	1280	Students and Examinations	CROSS JOIN / Aggregation
13	620	Not Boring Movies	WHERE / MOD
14	1251	Average Selling Price	JOIN / CASE WHEN / AVG
15	1075	Project Employees I	JOIN / AVG
16	1633	Percentage of Users Attended a Contest	JOIN / Subquery / Aggregation
17	1211	Queries Quality and Percentage	AVG / Conditional Aggregation
18	2356	Number of Unique Subjects Taught by Each Teacher	COUNT DISTINCT
19	1141	User Activity for the Past 30 Days I	Date Filtering / COUNT DISTINCT
20	596	Classes More Than 5 Students	GROUP BY / HAVING
21	1729	Find Followers Count	GROUP BY / COUNT
22	619	Biggest Single Number	Subquery / MAX / HAVING
23	1731	Number of Employees Reporting to Each Employee	Self JOIN / Aggregation
24	1789	Primary Department for Each Employee	UNION / Filtering
25	610	Triangle Judgement	CASE WHEN
26	1978	Employees Whose Manager Left the Company	Subquery / NOT IN
27	1667	Fix Names in a Table	UPPER / LOWER / CONCAT / SUBSTR
28	1527	Patients With a Condition	LIKE / String Matching
29	196	Delete Duplicate Emails	DELETE / Self JOIN
30	1484	Group Sold Products By The Date	GROUP_CONCAT / GROUP BY
31	1327	List the Products Ordered in a Period	JOIN / HAVING / Date Filter
32	1517	Find Users With Valid E-Mails	REGEXP / Pattern Validation

---

🟡 Medium — 17 Problems

#	LC #	Problem	Topic
01	570	Managers with at Least 5 Direct Reports	Self JOIN / GROUP BY / HAVING
02	1934	Confirmation Rate	LEFT JOIN / SUM(condition) / IFNULL
03	1193	Monthly Transactions I	CASE WHEN / Conditional Aggregation
04	1174	Immediate Food Delivery II	Subquery / MIN / Conditional AVG
05	550	Game Play Analysis IV	DATE_ADD / Subquery / COUNT
06	1070	Product Sales Analysis III	Subquery / IN with Tuple / MIN
07	1045	Customers Who Bought All Products	GROUP BY / COUNT DISTINCT vs Scalar Subquery
08	180	Consecutive Numbers	Self JOIN / 3-way Join
09	1164	Product Price at a Given Date	Subquery / UNION / Date Filtering
10	1204	Last Person to Fit in the Bus	Window Functions / Cumulative SUM
11	1907	Count Salary Categories	UNION ALL / Conditional COUNT
12	626	Exchange Seats	CASE WHEN / MOD / COUNT
13	1341	Movie Rating	UNION ALL / GROUP BY / ORDER BY / LIMIT
14	1321	Restaurant Growth	Window Functions / Moving Average / CTE
15	602	Friend Requests II: Who Has the Most Friends	UNION ALL / GROUP BY
16	585	Investments in 2016	Subquery / IN / COUNT > 1
17	176	Second Highest Salary	LIMIT+OFFSET / IFNULL / Subquery

---

🔴 Hard — 1 Problem

#	LC #	Problem	Topic
01	185	Department Top Three Salaries	DENSE_RANK / PARTITION BY / Subquery

---

🧩 Concept Index

Jump straight to any SQL technique and see every problem that uses it.

🔗 JOIN & NULL Handling — click to expand

Technique	Problems
INNER JOIN	1068 · 1075 · 570 · 185
LEFT JOIN	1378 · 1581 · 577 · 1934
CROSS JOIN	1280
Self JOIN	197 · 1731 · 570 · 180
NULL Handling (IS NULL, IFNULL, COALESCE)	584 · 577 · 1934 · 176

📊 Aggregation & Grouping — click to expand

Technique	Problems
GROUP BY + HAVING	596 · 1581 · 570 · 1045
COUNT DISTINCT	2356 · 1729 · 1141
SUM(condition) trick	1934 · 1211
Conditional aggregation (CASE WHEN inside SUM/AVG)	1193 · 1251 · 1174

🪟 Window Functions — click to expand

Technique	Problems
DENSE_RANK() OVER (PARTITION BY ...)	185
Cumulative SUM() OVER (ORDER BY ...)	1204
Moving Average (AVG OVER ROWS BETWEEN)	1321

🔀 Subqueries & Set Operations — click to expand

Technique	Problems
Scalar subquery in WHERE	619 · 1978 · 176
Correlated subquery / IN with tuple	1070 · 585
UNION ALL	1789 · 1907 · 1341 · 602
UNION (distinct)	1164

🔡 String & Pattern Matching — click to expand

Technique	Problems
LENGTH() vs CHAR_LENGTH()	1683
UPPER() / LOWER() / SUBSTR() / CONCAT()	1667
LIKE pattern	1527
REGEXP	1517
GROUP_CONCAT()	1484

📅 Date Functions — click to expand

Technique	Problems
DATEDIFF()	197
DATE_ADD()	550
DATE_FORMAT() / month filtering	1193 · 1141

💡 Conditional Logic — click to expand

Technique	Problems
CASE WHEN	1251 · 610 · 1193 · 626
IFNULL() / COALESCE()	1934 · 176
DELETE with Self JOIN	196

---

🧠 SQL Quick-Reference Cheatsheet

The most commonly tested patterns — pulled from these 50 problems.

-- ✅ Safe NULL comparison (never use = NULL)WHERE column IS NULLWHERE column IS NOT NULL-- ✅ LEFT JOIN to include non-matching rowsSELECTa.id, b.valueFROM TableA a
LEFT JOIN TableB b ONa.id=b.a_idWHEREb.a_id IS NULL-- rows in A with NO match in B-- ✅ SUM(condition) trick — count rows matching a conditionSELECTSUM(status ='confirmed') /COUNT(*) AS rate -- MySQL booleanFROM Orders
-- ✅ DENSE_RANK for top-N per group (no gaps on ties)SELECT*FROM (
SELECT name, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM Employee
) t
WHERE rnk <=3-- ✅ Moving average — last N rows inclusiveSELECTAVG(amount) OVER (
ORDER BY visit_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg
-- ✅ UNION ALL to combine then aggregate (preserve duplicates)SELECT id FROM TableA
UNION ALLSELECT id FROM TableB
-- ✅ GROUP_CONCAT for comma-separated strings per groupSELECT sell_date, COUNT(DISTINCT product) AS num_sold,
GROUP_CONCAT(DISTINCT product ORDER BY product) AS products
FROM Activities
GROUP BY sell_date
-- ✅ Second highest salary (handles NULL edge case)SELECT IFNULL(
(SELECT DISTINCT salary FROM Employee ORDER BY salary DESCLIMIT1 OFFSET 1),
NULL
) AS SecondHighestSalary
-- ✅ DELETE with self-join (avoid correlated subquery trap)DELETE p1
FROM Person p1
JOIN Person p2 ONp1.email=p2.emailANDp1.id>p2.id

---

🤝 Contributing

Found a better approach or a bug in my solution? PRs are welcome.

1. Fork the repo
2. Create a branch: git checkout -b fix/problem-number
3. Commit your change: git commit -m "fix: improved approach for #1934"
4. Open a pull request

---

If this helped you, a ⭐ keeps it alive.