---

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 NULL
WHERE column IS NOT NULL

-- ✅ LEFT JOIN to include non-matching rows
SELECT a.id, b.value
FROM TableA a
LEFT JOIN TableB b ON a.id = b.a_id
WHERE b.a_id IS NULL      -- rows in A with NO match in B

-- ✅ SUM(condition) trick — count rows matching a condition
SELECT SUM(status = 'confirmed') / COUNT(*) AS rate  -- MySQL boolean
FROM 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 inclusive
SELECT AVG(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 ALL
SELECT id FROM TableB

-- ✅ GROUP_CONCAT for comma-separated strings per group
SELECT 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 DESC LIMIT 1 OFFSET 1),
  NULL
) AS SecondHighestSalary

-- ✅ DELETE with self-join (avoid correlated subquery trap)
DELETE p1
FROM Person p1
JOIN Person p2 ON p1.email = p2.email AND p1.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.