Advanced SQL Coding Scenarios set - 3(21–33)

 

21. Find Duplicate Email IDs

SELECT Email,
       COUNT(*)
FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;

---

22. Customers With More Than 5 Orders

SELECT CustomerID,
       COUNT(*)
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 5;

---

23. Find Department Average Salary

SELECT DeptID,
       AVG(Salary)
FROM Employee
GROUP BY DeptID;

---

24. Employees Above Department Average

SELECT *
FROM Employee e
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
WHERE DeptID=e.DeptID
);

---

25. Employees Below Department Average

SELECT *
FROM Employee e
WHERE Salary <
(
SELECT AVG(Salary)
FROM Employee
WHERE DeptID=e.DeptID
);

---

26. Find Top Customer by Revenue

SELECT TOP 1
CustomerID,
SUM(Amount) Revenue
FROM Orders
GROUP BY CustomerID
ORDER BY Revenue DESC;

---

27. Top Product by Sales

SELECT TOP 1
ProductID,
SUM(Quantity)
FROM OrderDetails
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

---

28. Bottom Product by Sales

SELECT TOP 1
ProductID,
SUM(Quantity)
FROM OrderDetails
GROUP BY ProductID
ORDER BY SUM(Quantity);

---

29. Find Repeat Customers

SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 1;

---

30. Find One-Time Customers

SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) = 1;

---

31. Calculate Customer Lifetime Value

SELECT CustomerID,
       SUM(Amount)
FROM Orders
GROUP BY CustomerID;

---

32. Rank Customers by Revenue

SELECT CustomerID,
       SUM(Amount) Revenue,
       RANK() OVER
       (
       ORDER BY SUM(Amount) DESC
       ) rk
FROM Orders
GROUP BY CustomerID;

---

33. Top 10 Customers by Revenue

SELECT TOP 10
CustomerID,
SUM(Amount)
FROM Orders
GROUP BY CustomerID
ORDER BY SUM(Amount) DESC;