🔥 SQL Interview Case Studies (Advanced Business Scenarios) 💯

🧠 Case Study 1: Find Repeat Customers
📊 Orders Table
order_id customer_id
1 101
2 102
3 101

❓ Business Question
Find customers who placed more than 1 order.

✅ Solution
SELECT customer_id,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

🧠 Case Study 2: Highest Paid Employee Per Department
❓ Business Question
Find highest paid employee in every department.

✅ Solution
WITH RankedEmployees AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY department
ORDER BY salary DESC
) rn
FROM employees
)
SELECT *
FROM RankedEmployees
WHERE rn = 1;

🧠 Case Study 3: Find Inactive Customers
❓ Business Question
Customers who haven't ordered in the last 6 months.

✅ Solution
SELECT c.customer_id,
c.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING MAX(o.order_date) <
DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

🧠 Case Study 4: Product Generating Highest Revenue
📊 Sales Table
product_id amount

✅ Solution
SELECT product_id,
SUM(amount) AS revenue
FROM sales
GROUP BY product_id
ORDER BY revenue DESC
LIMIT 1;

🧠 Case Study 5: Employee Salary Above Department Average

✅ Solution
WITH DeptAvg AS (
SELECT department,
AVG(salary) avg_salary
FROM employees
GROUP BY department
)

SELECT e.*
FROM employees e
JOIN DeptAvg d
ON e.department = d.department
WHERE e.salary > d.avg_salary;

🧠 Case Study 6: Running Total Sales
❓ Business Question
Calculate cumulative sales.

✅ Solution
SELECT order_date,
amount,
SUM(amount) OVER(
ORDER BY order_date
) AS running_total
FROM sales;

🧠 Case Study 7: Top 3 Products Per Category

✅ Solution
WITH RankedProducts AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY category
ORDER BY sales DESC
) rn
FROM products
)
SELECT *
FROM RankedProducts
WHERE rn <= 3;

🎯 Practice Tasks
1️⃣ Find customer with highest number of orders
2️⃣ Find lowest salary employee per department
3️⃣ Find products never sold
4️⃣ Find departments with average salary > company average
5️⃣ Find month with lowest sales

⚡ Mini Challenge 🔥
Banking Scenario
Tables:
Accounts
account_id customer_name

Transactions
transaction_id account_id amount transaction_date

Question
👉 Find the top 3 customers with highest total transaction amount in the last 1 year.

🔥 Interview Tip
When solving case studies:
1️⃣ Understand business question
2️⃣ Identify tables
3️⃣ Identify JOINs
4️⃣ Identify Aggregations
5️⃣ Decide whether Window Function is needed

Double Tap ❤️ For More