🚀 Data Analyst Roadmap — Part 18
🧠 SQL Level 8 — Advanced Analytical Queries & Business Problems
At this stage, you know the core SQL building blocks:
SELECT → WHERE → GROUP BY → HAVING → JOIN → CTE → Window Functions → Date Analysis
Now it's time to combine them.
Real Data Analyst work rarely asks: "Write a query using RANK()"
Instead, you'll get business questions like:
• "Which customers are becoming inactive?"
• "What are our top-selling products in each category?"
• "Which month had the highest revenue growth?"
The real skill is converting a business problem into SQL logic.
🔹 1. Start With the Business Question
Before writing SQL, identify:
• What are we measuring?
• At what level?
• Which tables contain the required data?
• What filters are needed?
• Do we need aggregation?
• Do we need ranking or comparison?
For example: "Find the top 3 products in every category."
Break it down: Product → Category → Sales → Rank within Category → Keep Top 3
🔹 2. Find the Correct Grain
Grain means: What does one row represent?
• Orders → one row per order
• Order_Items → one row per product within an order
• Customers → one row per customer
If you don't understand the grain, you can accidentally double-count revenue.
🔹 3. Revenue by Customer
SELECT
c.Customer_ID,
c.Customer_Name,
SUM(o.Sales) AS Total_Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
GROUP BY c.Customer_ID, c.Customer_Name;
🔹 4. Rank Customers by Revenue
WITH Customer_Sales AS (
SELECT Customer_ID, SUM(Sales) AS Total_Sales
FROM Orders GROUP BY Customer_ID
)
SELECT
Customer_ID,
Total_Sales,
RANK() OVER (ORDER BY Total_Sales DESC) AS Sales_Rank
FROM Customer_Sales;
🔹 5. Top 3 Customers in Each Region
WITH Customer_Sales AS (
SELECT Customer_ID, Region, SUM(Sales) AS Total_Sales
FROM Orders GROUP BY Customer_ID, Region
),
Ranked_Customers AS (
SELECT *, RANK() OVER (PARTITION BY Region ORDER BY Total_Sales DESC) AS Sales_Rank
FROM Customer_Sales
)
SELECT * FROM Ranked_Customers WHERE Sales_Rank <= 3;
🔹 6. Finding the Second-Highest Salary
WITH Ranked_Employees AS (
SELECT Employee, Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees
)
SELECT Employee, Salary FROM Ranked_Employees WHERE Salary_Rank = 2;
🔹 7. Find Products That Never Sold
SELECT p.Product_ID, p.Product_Name
FROM Products p
LEFT JOIN Order_Items oi ON p.Product_ID = oi.Product_ID
WHERE oi.Product_ID IS NULL;
🔹 8. Customers With No Orders
SELECT c.Customer_ID, c.Customer_Name
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Customer_ID IS NULL;
🔹 9. Customers Above Average Spending
WITH Customer_Sales AS (
SELECT Customer_ID, SUM(Sales) AS Total_Sales
FROM Orders GROUP BY Customer_ID
)
SELECT Customer_ID, Total_Sales FROM Customer_Sales
WHERE Total_Sales > (SELECT AVG(Total_Sales) FROM Customer_Sales);
🔹 10. Month-over-Month Sales Growth
WITH Monthly_Sales AS (
SELECT EXTRACT(YEAR FROM Order_Date) AS Year,
EXTRACT(MONTH FROM Order_Date) AS Month,
SUM(Sales) AS Total_Sales
FROM Orders GROUP BY 1, 2
),
Comparison AS (
SELECT Year, Month, Total_Sales,
LAG(Total_Sales) OVER (ORDER BY Year, Month) AS Previous_Sales
FROM Monthly_Sales
)
SELECT Year, Month, Total_Sales, Previous_Sales,
Total_Sales - Previous_Sales AS Sales_Change
FROM Comparison;
