🧩 SQL Challenges

100 documented SQL challenges solved and explained — from fundamentals to query optimization. Built on MySQL 8 using the classic Northwind database.

Each challenge includes: the problem, my solution, an explanation of the reasoning, and alternative approaches when relevant.

📂 Structure

Folder	Topic	Status
joins/	INNER, LEFT, RIGHT, SELF and CROSS joins	🚧 in progress
aggregations/	GROUP BY, HAVING, conditional aggregation	🚧 in progress
subqueries/	Correlated and non-correlated subqueries	📋 planned
cte/	Common Table Expressions & recursion	📋 planned
window-functions/	RANK, ROW_NUMBER, LAG/LEAD, running totals	📋 planned
indexes/	Index design and EXPLAIN analysis	📋 planned
optimization/	Rewriting slow queries, execution plans	📋 planned

📄 Challenge format

Every challenge follows the same template:

-- Challenge #001 — Top 5 customers by total order value
-- Difficulty: ⭐⭐
-- Concepts: JOIN, GROUP BY, ORDER BY, LIMIT

-- Problem:
-- Return the 5 customers with the highest total purchase value.

SELECT c.company_name,
       ROUND(SUM(od.unit_price * od.quantity * (1 - od.discount)), 2) AS total_value
FROM customers c
JOIN orders o        ON o.customer_id = c.id
JOIN order_details od ON od.order_id  = o.id
GROUP BY c.company_name
ORDER BY total_value DESC
LIMIT 5;

-- Why this works:
-- We aggregate at the customer level after joining the three tables,
-- applying the discount before summing.

🎯 Goal

25–35 challenges solved per week, every week. Consistency over intensity.

👨🏾‍💻 Author

Judson Paiva — Data Engineer & University Lecturer (Databases, 8+ years) LinkedIn · GitHub