🧠 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
• This approach prevents complicated SQL from becoming confusing.
🔹 2. Find the Correct Grain
One of the most important analytical concepts is grain.
Grain means: What does one row represent?
For example:
• 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
Suppose you have:
Customers
• Customer_ID
• Customer_Name
and Orders
• Order_ID
• Customer_ID
• Order_Date
• Sales
You can calculate customer revenue:
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;
• Now you have one row per customer.
🔹 4. Rank Customers by Revenue
Combine aggregation with a window function:
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;
• This answers: Who are our highest-value customers?
🔹 5. Top 3 Customers in Each Region
Now add another business dimension.
WITH Customer_Sales AS (
SELECT Customer_ID, Region, SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY Customer_ID, Region
),
Ranked_Customers AS (
SELECT
Customer_ID, Region, Total_Sales,
RANK() OVER (PARTITION BY Region ORDER BY Total_Sales DESC) AS Sales_Rank
FROM Customer_Sales
)
SELECT * FROM Ranked_Customers WHERE Sales_Rank <= 3;
• This is a classic advanced SQL interview problem.
🔹 6. Finding the Second-Highest Salary
A common interview question.
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;
• Using DENSE_RANK() is useful when multiple employees share the same salary.
🔹 7. Find Products That Never Sold
This is a classic LEFT JOIN problem.
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;
Business interpretation: Products exist in the catalog but have no sales.
This could indicate:
• Poor demand
• Pricing problems
• Inventory issues
• Product visibility problems
🔹 8. Customers With No Orders
The same logic can identify customers who have never purchased.
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;