Advanced SQL Coding Scenarios set - 2(11–20)

 

11. Find Latest Order Per Customer

SELECT *
FROM
(
SELECT *,
       ROW_NUMBER() OVER
       (
       PARTITION BY CustomerID
       ORDER BY OrderDate DESC
       ) rn
FROM Orders
)x
WHERE rn=1;

---

12. Find First Order Per Customer

SELECT *
FROM
(
SELECT *,
       ROW_NUMBER() OVER
       (
       PARTITION BY CustomerID
       ORDER BY OrderDate
       ) rn
FROM Orders
)x
WHERE rn=1;

---

13. Find Customers With No Orders

SELECT c.*
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID=o.CustomerID
WHERE o.CustomerID IS NULL;

---

14. Find Products Never Sold

SELECT p.*
FROM Products p
LEFT JOIN OrderDetails od
ON p.ProductID=od.ProductID
WHERE od.ProductID IS NULL;

---

15. Running Total of Sales

SELECT SalesDate,
       Amount,
       SUM(Amount)
       OVER(ORDER BY SalesDate)
       RunningTotal
FROM Sales;

---

16. Month-over-Month Sales Growth

SELECT Month,
       Sales,
       LAG(Sales)
       OVER(ORDER BY Month) PrevMonth,
       Sales -
       LAG(Sales)
       OVER(ORDER BY Month) Growth
FROM Sales;

---

17. Year-over-Year Growth

SELECT Year,
       Sales,
       LAG(Sales)
       OVER(ORDER BY Year) PrevYear
FROM Sales;

---

18. Find Consecutive Duplicate Values

SELECT *
FROM
(
SELECT *,
       LAG(Status)
       OVER(ORDER BY ID) PrevStatus
FROM Orders
)x
WHERE Status = PrevStatus;

---

19. Find Missing Invoice Numbers

SELECT InvoiceNo+1 MissingNo
FROM Invoices
WHERE InvoiceNo+1 NOT IN
(
SELECT InvoiceNo
FROM Invoices
);

---

20. Find Gaps in Sequence

SELECT ID,
       LEAD(ID)
       OVER(ORDER BY ID) NextID
FROM Employee;

Check where:

NextID - ID > 1