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