Sql Server Complex queries asking in interviews

Here is a list of complex SQL Server queries that might be asked in interviews, which cover advanced SQL concepts such as subqueries, window functions, CTEs, and more:

Complex Queries

1. Find Top N Salaries by Department

   WITH RankedSalaries AS (
       SELECT Department, Salary,
              ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
       FROM Employees
   )
   SELECT Department, Salary
   FROM RankedSalaries
   WHERE Rank <= 5;
   

2. Find the Previous and Next Employee Salaries

   SELECT EmployeeID, Salary,
          LAG(Salary, 1) OVER (ORDER BY Salary) AS PreviousSalary,
          LEAD(Salary, 1) OVER (ORDER BY Salary) AS NextSalary
   FROM Employees;
   

3. Get the Employees Who Earn More Than the Average Salary of Their Department

   WITH AvgSalaries AS (
       SELECT Department, AVG(Salary) AS AvgSalary
       FROM Employees
       GROUP BY Department
   )
   SELECT e.EmployeeID, e.FirstName, e.Salary
   FROM Employees e
   INNER JOIN AvgSalaries a ON e.Department = a.Department
   WHERE e.Salary > a.AvgSalary;
   

4. Find Employees with Duplicate Names in Different Departments

   SELECT FirstName, LastName, COUNT(*)
   FROM Employees
   GROUP BY FirstName, LastName
   HAVING COUNT(DISTINCT Department) > 1;
   

5. Get Hierarchical Data Using Recursive CTE

   WITH EmployeeHierarchy AS (
       SELECT EmployeeID, ManagerID, FirstName, 1 AS Level
       FROM Employees
       WHERE ManagerID IS NULL
       UNION ALL
       SELECT e.EmployeeID, e.ManagerID, e.FirstName, h.Level + 1
       FROM Employees e
       INNER JOIN EmployeeHierarchy h ON e.ManagerID = h.EmployeeID
   )
   SELECT * FROM EmployeeHierarchy;
   

6. Find Departments with More Than the Average Number of Employees

   WITH DeptCounts AS (
       SELECT Department, COUNT(*) AS DeptCount
       FROM Employees
       GROUP BY Department
   ),
   AvgDeptCount AS (
       SELECT AVG(DeptCount) AS AvgCount
       FROM DeptCounts
   )
   SELECT Department
   FROM DeptCounts
   WHERE DeptCount > (SELECT AvgCount FROM AvgDeptCount);
   

7. Calculate Running Total of Salaries

   SELECT EmployeeID, Salary,
          SUM(Salary) OVER (ORDER BY EmployeeID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
   FROM Employees;
   

8. Find Gaps in Employee IDs

   SELECT e1.EmployeeID + 1 AS MissingID
   FROM Employees e1
   LEFT JOIN Employees e2 ON e1.EmployeeID + 1 = e2.EmployeeID
   WHERE e2.EmployeeID IS NULL
   ORDER BY e1.EmployeeID;
   

9. Get Monthly Sales Growth

   SELECT SalesMonth, SalesAmount,
          LAG(SalesAmount, 1) OVER (ORDER BY SalesMonth) AS PreviousMonthSales,
          SalesAmount - LAG(SalesAmount, 1) OVER (ORDER BY SalesMonth) AS Growth
   FROM Sales;
   

10. Find Employees with the Highest Salary in Their Department

    WITH MaxSalaries AS (
        SELECT Department, MAX(Salary) AS MaxSalary
        FROM Employees
        GROUP BY Department
    )
    SELECT e.EmployeeID, e.FirstName, e.Salary
    FROM Employees e
    INNER JOIN MaxSalaries m ON e.Department = m.Department AND e.Salary = m.MaxSalary;
    

11. List Customers Who Made Purchases in All Years

    SELECT CustomerID
    FROM Purchases
    GROUP BY CustomerID
    HAVING COUNT(DISTINCT YEAR(PurchaseDate)) = (SELECT COUNT(DISTINCT YEAR(PurchaseDate)) FROM Purchases);
    

12. Get the Most Recent Purchase for Each Customer

    SELECT CustomerID, MAX(PurchaseDate) AS MostRecentPurchase
    FROM Purchases
    GROUP BY CustomerID;
    

13. Find Products That Have Not Been Sold

    SELECT p.ProductID, p.ProductName
    FROM Products p
    LEFT JOIN Sales s ON p.ProductID = s.ProductID
    WHERE s.ProductID IS NULL;
    

14. Find the Top 3 Products with the Highest Total Sales

    SELECT TOP 3 p.ProductID, p.ProductName, SUM(s.SalesAmount) AS TotalSales
    FROM Products p
    INNER JOIN Sales s ON p.ProductID = s.ProductID
    GROUP BY p.ProductID, p.ProductName
    ORDER BY TotalSales DESC;
    

15. Compare Sales for the Current Year to the Previous Year

    WITH SalesComparison AS (
        SELECT YEAR(SaleDate) AS SaleYear, SUM(SalesAmount) AS TotalSales
        FROM Sales
        GROUP BY YEAR(SaleDate)
    )
    SELECT c.SaleYear AS CurrentYear, p.SaleYear AS PreviousYear,
           c.TotalSales - p.TotalSales AS SalesDifference
    FROM SalesComparison c
    LEFT JOIN SalesComparison p ON c.SaleYear = p.SaleYear + 1;
    

16. Calculate Yearly Average Sales Growth

    WITH YearlySales AS (
        SELECT YEAR(SaleDate) AS SaleYear, SUM(SalesAmount) AS TotalSales
        FROM Sales
        GROUP BY YEAR(SaleDate)
    )
    SELECT SaleYear,
           TotalSales,
           LAG(TotalSales, 1) OVER (ORDER BY SaleYear) AS PreviousYearSales,
           (TotalSales - LAG(TotalSales, 1) OVER (ORDER BY SaleYear)) / LAG(TotalSales, 1) OVER (ORDER BY SaleYear) * 100 AS GrowthPercentage
    FROM YearlySales;
    

17. Find Orders with No Items

    SELECT o.OrderID
    FROM Orders o
    LEFT JOIN OrderItems oi ON o.OrderID = oi.OrderID
    WHERE oi.OrderID IS NULL;
    

18. Find the Most Frequent Product Category

    SELECT TOP 1 Category, COUNT(*) AS Frequency
    FROM Products
    GROUP BY Category
    ORDER BY Frequency DESC;
    

19. Find Employees Who Have Not Received a Raise in the Last Year

    SELECT EmployeeID, FirstName
    FROM Employees
    WHERE LastRaiseDate < DATEADD(YEAR, -1, GETDATE());
    

20. Get a List of All Employees and Their Managers

    SELECT e.EmployeeID AS EmployeeID, e.FirstName AS EmployeeName, m.FirstName AS ManagerName
    FROM Employees e
    LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;
    

21. Find Employees Who Have Worked in Multiple Departments

    SELECT EmployeeID
    FROM Employees
    GROUP BY EmployeeID
    HAVING COUNT(DISTINCT DepartmentID) > 1;
    

22. Get the Average Salary for Each Manager

    SELECT ManagerID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY ManagerID;
    

23. Find the Departments with the Highest Turnover Rate

    WITH EmployeeCounts AS (
        SELECT DepartmentID, COUNT(*) AS NumEmployees
        FROM Employees
        GROUP BY DepartmentID
    ),
    Departures AS (
        SELECT DepartmentID, COUNT(*) AS NumDepartures
        FROM Departures
        GROUP BY DepartmentID
    )
    SELECT e.DepartmentID, (NumDepartures * 1.0 / NumEmployees) AS TurnoverRate
    FROM EmployeeCounts e
    INNER JOIN Departures d ON e.DepartmentID = d.DepartmentID
    ORDER BY TurnoverRate DESC;
    

24. Get Top 5 Products by Revenue for Each Category

    WITH ProductRevenue AS (
        SELECT p.Category, p.ProductID, p.ProductName, SUM(s.SalesAmount) AS TotalRevenue
        FROM Products p
        INNER JOIN Sales s ON p.ProductID = s.ProductID
        GROUP BY p.Category, p.ProductID, p.ProductName
    )
    SELECT Category, ProductID, ProductName, TotalRevenue
    FROM (
        SELECT Category, ProductID, ProductName, TotalRevenue,
               ROW_NUMBER() OVER (PARTITION BY Category ORDER BY TotalRevenue DESC) AS Rank
        FROM ProductRevenue
    ) AS RankedProducts
    WHERE Rank <= 5;
    

25. Get the Date of the First Sale for Each Product

    SELECT ProductID, MIN(SaleDate) AS FirstSaleDate
    FROM Sales
    GROUP BY ProductID;
    

26. Find Employees Whose Salary is Above the Department Average and Their Managers' Salaries Are Below the Average

    WITH DepartmentAverages AS (
        SELECT DepartmentID, AVG(Salary) AS AvgSalary
        FROM Employees
        GROUP BY DepartmentID
    ),
    EmployeeSalaries AS (
        SELECT e.EmployeeID, e.Salary, e.ManagerID, d.AvgSalary
        FROM Employees e
        INNER JOIN DepartmentAverages d ON e.DepartmentID = d.DepartmentID
    )
    SELECT e.EmployeeID, e.Salary
    FROM EmployeeSalaries e
    INNER JOIN EmployeeSalaries m ON e.ManagerID = m.EmployeeID
    WHERE e.Salary > e.AvgSalary AND m.Salary < e.AvgSalary;
    

27. Get the Top 3 Most Expensive Items Sold in Each Store

    WITH RankedItems AS (
        SELECT StoreID, ItemID, ItemName, SalePrice,
               ROW_NUMBER() OVER (PARTITION BY StoreID ORDER BY SalePrice DESC) AS Rank
        FROM Sales
    )
    SELECT StoreID, ItemID, ItemName, SalePrice
    FROM RankedItems
    WHERE Rank <= 3;
    

28. List Customers with More Than 5 Orders in the Last Month

    SELECT CustomerID, COUNT(*) AS NumberOfOrders
    FROM Orders
    WHERE OrderDate > DATEADD(MONTH, -1, GETDATE())
    GROUP BY CustomerID
    HAVING COUNT(*) > 5;
    

29. Find Employees Who Have a Salary Higher than the Highest Salary of Their Manager

    WITH ManagerSalaries AS (
        SELECT e.EmployeeID, e.Salary AS EmployeeSalary, m.Salary AS ManagerSalary
        FROM Employees e
        INNER JOIN Employees m ON e.ManagerID = m.EmployeeID
    )
    SELECT EmployeeID, EmployeeSalary
    FROM ManagerSalaries
    WHERE EmployeeSalary > ManagerSalary;
    

30. Calculate the Cumulative Sales for Each Product

    SELECT ProductID, SaleDate, SalesAmount,
           SUM(SalesAmount) OVER (PARTITION BY ProductID ORDER BY SaleDate) AS CumulativeSales
    FROM Sales;
    

31. Find the Most Common Purchase Date

    SELECT TOP 1 PurchaseDate, COUNT(*) AS Frequency
    FROM Purchases
    GROUP BY PurchaseDate
    ORDER BY Frequency DESC;
    

32. Find Customers Who Made Purchases in Consecutive Months

    WITH MonthlyPurchases AS (
        SELECT CustomerID, YEAR(PurchaseDate) AS PurchaseYear, MONTH(PurchaseDate) AS PurchaseMonth
        FROM Purchases
        GROUP BY CustomerID, YEAR(PurchaseDate), MONTH(PurchaseDate)
    ),
    ConsecutiveMonths AS (
        SELECT CustomerID, PurchaseYear, PurchaseMonth,
               LEAD(PurchaseYear, 1) OVER (PARTITION BY CustomerID ORDER BY PurchaseYear, PurchaseMonth) AS NextYear,
               LEAD(PurchaseMonth, 1) OVER (PARTITION BY CustomerID ORDER BY PurchaseYear, PurchaseMonth) AS NextMonth
        FROM MonthlyPurchases
    )
    SELECT CustomerID
    FROM ConsecutiveMonths
    WHERE (NextYear = PurchaseYear AND NextMonth = PurchaseMonth + 1) OR
          (NextYear = PurchaseYear + 1 AND NextMonth = 1 AND PurchaseMonth = 12);
    

33. List Products That Have Not Been Sold in the Last Year

    SELECT p.ProductID, p.ProductName
    FROM Products p
    LEFT JOIN Sales s ON p.ProductID = s.ProductID AND s.SaleDate > DATEADD(YEAR, -1, GETDATE())
    WHERE s.ProductID IS NULL;
    

34. Calculate the Percentage Change in Sales Month-over-Month

    WITH MonthlySales AS (
        SELECT YEAR(SaleDate) AS SaleYear, MONTH(SaleDate) AS SaleMonth, SUM(SalesAmount) AS TotalSales
        FROM Sales
        GROUP BY YEAR(SaleDate), MONTH(SaleDate)
    )
    SELECT SaleYear, SaleMonth, TotalSales,
           LAG(TotalSales, 1) OVER (ORDER BY SaleYear, SaleMonth) AS PreviousMonthSales,
           (TotalSales - LAG(TotalSales, 1) OVER (ORDER BY SaleYear, SaleMonth)) * 100.0 / LAG(TotalSales, 1) OVER (ORDER BY SaleYear, SaleMonth) AS PercentageChange
    FROM MonthlySales;
    

35. Find Employees Who Have Worked on All Projects

    
    SELECT EmployeeID
    FROM ProjectAssignments
    GROUP BY EmployeeID
    HAVING COUNT(DISTINCT ProjectID) = (SELECT COUNT(DISTINCT ProjectID) FROM Projects);
    

36. Find the Top 5 Products with the Highest Average Rating

    SELECT TOP 5 p.ProductID, p.ProductName, AVG(r.Rating) AS AvgRating
    FROM Products p
    INNER JOIN Reviews r ON p.ProductID = r.ProductID
    GROUP BY p.ProductID, p.ProductName
    ORDER BY AvgRating DESC;
    

37. List Employees with the Most Direct Reports

    SELECT ManagerID, COUNT(*) AS NumberOfReports
    FROM Employees
    GROUP BY ManagerID
    ORDER BY NumberOfReports DESC;
    

38. Find Orders with the Highest Total Amount in Each Year

    WITH YearlyOrderTotals AS (
        SELECT YEAR(OrderDate) AS OrderYear, OrderID, SUM(Amount) AS TotalAmount
        FROM OrderDetails
        GROUP BY YEAR(OrderDate), OrderID
    )
    SELECT OrderYear, OrderID, TotalAmount
    FROM (
        SELECT OrderYear, OrderID, TotalAmount,
               ROW_NUMBER() OVER (PARTITION BY OrderYear ORDER BY TotalAmount DESC) AS Rank
        FROM YearlyOrderTotals
    ) AS RankedOrders
    WHERE Rank = 1;
    

39. Get the Most Expensive Item Purchased by Each Customer

    SELECT CustomerID, MAX(SaleAmount) AS MostExpensiveItem
    FROM Sales
    GROUP BY CustomerID;
    

40. Find the Longest Consecutive Sales Days

    WITH DateSequences AS (
        SELECT SaleDate,
               ROW_NUMBER() OVER (ORDER BY SaleDate) - ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY SaleDate) AS Sequence
        FROM Sales
    )
    SELECT MIN(SaleDate) AS StartDate, MAX(SaleDate) AS EndDate, COUNT(*) AS ConsecutiveDays
    FROM DateSequences
    GROUP BY Sequence
    ORDER BY ConsecutiveDays DESC;
    

41. List Employees Who Have Not Been Assigned to Any Project

    SELECT e.EmployeeID, e.FirstName
    FROM Employees e
    LEFT JOIN ProjectAssignments pa ON e.EmployeeID = pa.EmployeeID
    WHERE pa.EmployeeID IS NULL;
    

42. Find the Total Sales for Each Product Category Over Time

    SELECT p.Category, YEAR(s.SaleDate) AS SaleYear, SUM(s.SalesAmount) AS TotalSales
    FROM Sales s
    INNER JOIN Products p ON s.ProductID = p.ProductID
    GROUP BY p.Category, YEAR(s.SaleDate)
    ORDER BY p.Category, SaleYear;
    

43. Find the Employees Who Have Received the Highest Number of Bonuses

    SELECT EmployeeID, COUNT(*) AS NumberOfBonuses
    FROM Bonuses
    GROUP BY EmployeeID
    ORDER BY NumberOfBonuses DESC;
    

44. Get the Average Order Amount for Each Customer by Year

    SELECT CustomerID, YEAR(OrderDate) AS OrderYear, AVG(TotalAmount) AS AvgOrderAmount
    FROM Orders
    GROUP BY CustomerID, YEAR(OrderDate)
    ORDER BY CustomerID, OrderYear;
    

45. Calculate the Total Revenue Generated by Each Salesperson

    SELECT SalespersonID, SUM(TotalAmount) AS TotalRevenue
    FROM Sales
    GROUP BY SalespersonID;
    

46. Find Products That Have Been Sold with All Possible Discounts

    WITH AllDiscounts AS (
        SELECT DISTINCT DiscountRate
        FROM Discounts
    ),
    ProductDiscounts AS (
        SELECT ProductID, COUNT(DISTINCT DiscountRate) AS DiscountCount
        FROM Sales
        INNER JOIN Discounts ON Sales.DiscountID = Discounts.DiscountID
        GROUP BY ProductID
    )
    SELECT p.ProductID, p.ProductName
    FROM Products p
    INNER JOIN ProductDiscounts pd ON p.ProductID = pd.ProductID
    WHERE pd.DiscountCount = (SELECT COUNT(*) FROM AllDiscounts);
    

47. Find the Highest Revenue Day for Each Product

    WITH DailySales AS (
        SELECT ProductID, SaleDate, SUM(SalesAmount) AS DailyRevenue
        FROM Sales
        GROUP BY ProductID, SaleDate
    )
    SELECT ProductID, SaleDate, DailyRevenue
    FROM (
        SELECT ProductID, SaleDate, DailyRevenue,
               ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY DailyRevenue DESC) AS Rank
        FROM DailySales
    ) AS RankedSales
    WHERE Rank = 1;
    

48. Get the Employee Who Has Worked the Longest Consecutive Days

    WITH ConsecutiveDays AS (
        SELECT EmployeeID, WorkDate,
               ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY WorkDate) - ROW_NUMBER() OVER (ORDER BY WorkDate) AS GroupNum
        FROM WorkHistory
    )
    SELECT EmployeeID, MIN(WorkDate) AS StartDate, MAX(WorkDate) AS EndDate, COUNT(*) AS ConsecutiveDays
    FROM ConsecutiveDays
    GROUP BY EmployeeID, GroupNum
    ORDER BY ConsecutiveDays DESC
    OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;
    

49. Find Customers Who Have Made Purchases in Each Quarter of the Year

    WITH QuarterlyPurchases AS (
        SELECT CustomerID, DATEPART(QUARTER, PurchaseDate) AS Quarter, YEAR(PurchaseDate) AS Year
        FROM Purchases
        GROUP BY CustomerID, DATEPART(QUARTER, PurchaseDate), YEAR(PurchaseDate)
    )
    SELECT CustomerID
    FROM QuarterlyPurchases
    GROUP BY CustomerID, Year
    HAVING COUNT(DISTINCT Quarter) = 4;
    

50. Find the Average Time Between Orders for Each Customer

    WITH OrderTimes AS (
        SELECT CustomerID, OrderDate,
               LAG(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PreviousOrderDate
        FROM Orders
    )
    SELECT CustomerID,
           AVG(DATEDIFF(DAY, PreviousOrderDate, OrderDate)) AS AvgDaysBetweenOrders
    FROM OrderTimes
    WHERE PreviousOrderDate IS NOT NULL
    GROUP BY CustomerID;
    

51. Find Products That Have Not Been Purchased in the Last 6 Months

    SELECT p.ProductID, p.ProductName
    FROM Products p
    LEFT JOIN Sales s ON p.ProductID = s.ProductID AND s.SaleDate > DATEADD(MONTH, -6, GETDATE())
    WHERE s.ProductID IS NULL;
    

52. Find the Customers Who Have Spent More Than the Average Purchase Amount

    WITH CustomerTotals AS (
        SELECT CustomerID, SUM(SalesAmount) AS TotalSpent
        FROM Sales
        GROUP BY CustomerID
    ),
    AvgSpent AS (
        SELECT AVG(TotalSpent) AS AvgAmount
        FROM CustomerTotals
    )
    SELECT CustomerID, TotalSpent
    FROM CustomerTotals
    WHERE TotalSpent > (SELECT AvgAmount FROM AvgSpent);
    

53. Find the Most Frequent Product Purchased by Each Customer

    WITH ProductCounts AS (
        SELECT CustomerID, ProductID, COUNT(*) AS PurchaseCount
        FROM Sales
        GROUP BY CustomerID, ProductID
    )
    SELECT CustomerID, ProductID, PurchaseCount
    FROM (
        SELECT CustomerID, ProductID, PurchaseCount,
               ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY PurchaseCount DESC) AS Rank
        FROM ProductCounts
    ) AS RankedProducts
    WHERE Rank = 1;
    

54. Find Employees Who Have Not Taken Any Leave in the Last Year

    SELECT e.EmployeeID, e.FirstName
    FROM Employees e
    LEFT JOIN Leaves l ON e.EmployeeID = l.EmployeeID AND l.LeaveDate > DATEADD(YEAR, -1, GETDATE())
    WHERE l.EmployeeID IS NULL;
    

55. Find the Top 5 Best-Selling Products Each Month

    WITH MonthlySales AS (
        SELECT YEAR(SaleDate) AS SaleYear, MONTH(SaleDate) AS SaleMonth, ProductID, SUM(SalesAmount) AS TotalSales
        FROM Sales
        GROUP BY YEAR(SaleDate), MONTH(SaleDate), ProductID
    )
    SELECT SaleYear, SaleMonth, ProductID, TotalSales
    FROM (
        SELECT SaleYear, SaleMonth, ProductID, TotalSales,
               ROW_NUMBER() OVER (PARTITION BY SaleYear, SaleMonth ORDER BY TotalSales DESC) AS Rank
        FROM MonthlySales
    ) AS RankedProducts
    WHERE Rank <= 5;
    

56. Calculate the Total and Average Sales for Each Store by Year

    SELECT StoreID, YEAR(SaleDate) AS SaleYear, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AvgSales
    FROM Sales
    GROUP BY StoreID, YEAR(SaleDate);
    

57. Find the Most Recent Transaction for Each Account

    SELECT AccountID, MAX(TransactionDate) AS MostRecentTransaction
    FROM Transactions
    GROUP BY AccountID;
    

58. Find the Top 3 Customers Who Have Spent the Most in Each Product Category

    WITH CustomerSpending AS (
        SELECT c.CustomerID, p.Category, SUM(s.SalesAmount) AS TotalSpent
        FROM Sales s
        INNER JOIN Products p ON s.ProductID = p.ProductID
        INNER JOIN Customers c ON s.CustomerID = c.CustomerID
        GROUP BY c.CustomerID, p.Category
    )
    SELECT Category, CustomerID, TotalSpent
    FROM (
        SELECT Category, CustomerID, TotalSpent,
               ROW_NUMBER() OVER (PARTITION BY Category ORDER BY TotalSpent DESC) AS Rank
        FROM CustomerSpending
    ) AS RankedSpending
    WHERE Rank <= 3;
    

59. Find the Most Profitable Month for Each Product

    WITH MonthlyProfits AS (
        SELECT ProductID, YEAR(SaleDate) AS SaleYear, MONTH(SaleDate) AS SaleMonth, SUM(Profit) AS TotalProfit
        FROM Sales
        GROUP BY ProductID, YEAR(SaleDate), MONTH(SaleDate)
    )
    SELECT ProductID, SaleYear, SaleMonth, TotalProfit
    FROM (
        SELECT ProductID, SaleYear, SaleMonth, TotalProfit,
               ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TotalProfit DESC) AS Rank
        FROM MonthlyProfits
    ) AS RankedProfits
    WHERE Rank = 1;
    

60. Find the Longest Consecutive Streak of Sales for Each Product

    WITH SalesStreaks AS (
        SELECT ProductID, SaleDate,
               ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY SaleDate) - ROW_NUMBER() OVER (ORDER BY SaleDate) AS StreakGroup
        FROM Sales
    )
    SELECT ProductID, MIN(SaleDate) AS StartDate, MAX(SaleDate) AS EndDate, COUNT(*) AS StreakLength
    FROM SalesStreaks
    GROUP BY ProductID, StreakGroup
    ORDER BY StreakLength DESC;
    

61. Find the Top 5 Customers with the Highest Purchase Frequency

    SELECT TOP 5 CustomerID, COUNT(*) AS PurchaseCount
    FROM Sales
    GROUP BY CustomerID
    ORDER BY PurchaseCount DESC;
    

62. Find the Most Expensive Purchase for Each Customer

    SELECT CustomerID, MAX(SalesAmount) AS MostExpensivePurchase
    FROM Sales
    GROUP BY CustomerID;
    

63. Find the Average Time Between Purchases for Each Customer

    WITH PurchaseIntervals AS (
        SELECT CustomerID, PurchaseDate,
               LAG(PurchaseDate, 1) OVER (PARTITION BY CustomerID ORDER BY PurchaseDate) AS PreviousPurchaseDate
        FROM Purchases
    )
    SELECT CustomerID,
           AVG(DATEDIFF(DAY, PreviousPurchaseDate, PurchaseDate)) AS AvgDaysBetweenPurchases
    FROM PurchaseIntervals
    WHERE PreviousPurchaseDate IS NOT NULL
    GROUP BY CustomerID;
    

64. Get the Top 5 Customers by Total Revenue in the Last Year

    SELECT TOP 5 CustomerID, SUM(SalesAmount) AS TotalRevenue
    FROM Sales
    WHERE SaleDate > DATEADD(YEAR, -1, GETDATE())
    GROUP BY CustomerID
    ORDER BY TotalRevenue DESC;
    

65. Find the Top 5 Products by Revenue in the Last 30 Days

    SELECT TOP 5 ProductID, SUM(SalesAmount) AS TotalRevenue
    FROM Sales
    WHERE SaleDate > DATEADD(DAY, -30, GETDATE())
    GROUP BY ProductID
    ORDER BY TotalRevenue DESC;
    

66. Get the Monthly Revenue Growth Rate

    WITH MonthlyRevenue AS (
        SELECT YEAR(SaleDate) AS SaleYear, MONTH(SaleDate) AS SaleMonth, SUM(SalesAmount) AS TotalRevenue
        FROM Sales
        GROUP BY YEAR(SaleDate), MONTH(SaleDate)
    )
    SELECT SaleYear, SaleMonth, TotalRevenue,
           LAG(TotalRevenue, 1) OVER (ORDER BY SaleYear, SaleMonth) AS PreviousMonthRevenue,
           (TotalRevenue - LAG(TotalRevenue, 1) OVER (ORDER BY SaleYear, SaleMonth)) * 100.0 / LAG(TotalRevenue, 1) OVER (ORDER BY SaleYear, SaleMonth) AS GrowthRate
    FROM MonthlyRevenue;
    

67. Find the Most Popular Product Category for Each Region

    WITH RegionalSales AS (
        SELECT r.Region, p.Category, SUM(s.SalesAmount) AS TotalSales
        FROM Sales s
        INNER JOIN Products p ON s.ProductID = p.ProductID
        INNER JOIN Regions r ON s.RegionID = r.RegionID
        GROUP BY r.Region, p.Category
    )
    SELECT Region, Category, TotalSales
    FROM (
        SELECT Region, Category, TotalSales,
               ROW_NUMBER() OVER (PARTITION BY Region ORDER BY TotalSales DESC) AS Rank
        FROM RegionalSales
    ) AS RankedCategories
    WHERE Rank = 1;
    

68. Find the Most Frequent Sales Date

    SELECT TOP 1 SaleDate, COUNT(*) AS Frequency
    FROM Sales
    GROUP BY SaleDate
    ORDER BY Frequency DESC;
    

69. Find the Customers Who Have Not Made Any Purchases in the Last 6 Months

    SELECT c.CustomerID, c.CustomerName
    FROM Customers c
    LEFT JOIN Sales s ON c.CustomerID = s.CustomerID AND s.SaleDate > DATEADD(MONTH, -6, GETDATE())
    WHERE s.CustomerID IS NULL;
    

70. Find the Products with the Highest Average Rating

    SELECT p.ProductID, p.ProductName, AVG(r.Rating) AS AvgRating
    FROM Products p
    INNER JOIN Reviews r ON p.ProductID = r.ProductID
    GROUP BY p.ProductID, p.ProductName
    ORDER BY AvgRating DESC;
    

71. Find the Average Revenue Generated by Each Product Category

    SELECT p.Category, AVG(s.SalesAmount) AS AvgRevenue
    FROM Sales s
    INNER JOIN Products p ON s.ProductID = p.ProductID
    GROUP BY p.Category;
    

72. Find the Most Recent Review for Each Product

    SELECT ProductID, MAX(ReviewDate) AS MostRecentReview
    FROM Reviews
    GROUP BY ProductID;
    

73. Find the Top 5 Products by Number of Sales

    SELECT TOP 5 ProductID, COUNT(*) AS NumberOfSales
    FROM Sales
    GROUP BY ProductID
    ORDER BY NumberOfSales DESC;
    

74. Find the Top 5 Customers by Average Order Amount

    WITH CustomerOrders AS (
        SELECT CustomerID, AVG(OrderAmount) AS AvgOrderAmount
        FROM Orders
        GROUP BY CustomerID
    )
    SELECT TOP 5 CustomerID, AvgOrderAmount
    FROM CustomerOrders
    ORDER BY AvgOrderAmount DESC;
    

75. Find the Products That Have Been Sold in Each Store

    SELECT ProductID
    FROM Sales
    GROUP BY ProductID
    HAVING COUNT(DISTINCT StoreID) = (SELECT COUNT(DISTINCT StoreID) FROM Sales);
    

76. Find the Top 5 Regions by Total Sales

    SELECT TOP 5 r.Region, SUM(s.SalesAmount) AS TotalSales
    FROM Sales s
    INNER JOIN Regions r ON s.RegionID = r.RegionID
    GROUP BY r.Region
    ORDER BY TotalSales DESC;
    

77. Find the Most Common Product Purchased by Each Customer

    WITH ProductCounts AS (
        SELECT CustomerID, ProductID, COUNT(*) AS PurchaseCount
        FROM Sales
        GROUP BY CustomerID, ProductID
    )
    SELECT CustomerID, ProductID, PurchaseCount
    FROM (
        SELECT CustomerID, ProductID, PurchaseCount,
               ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY PurchaseCount DESC) AS Rank
        FROM ProductCounts
    ) AS RankedProducts
    WHERE Rank = 1;
    

78. Find the Total Revenue for Each Store by Month

    SELECT StoreID, YEAR(SaleDate) AS SaleYear, MONTH(SaleDate) AS SaleMonth, SUM(SalesAmount) AS TotalRevenue
    FROM Sales
    GROUP BY StoreID, YEAR(SaleDate), MONTH(SaleDate)
    ORDER BY StoreID, SaleYear, SaleMonth;
    

79. Find the Average Revenue for Each Product Category

    SELECT p.Category, AVG(s.SalesAmount) AS AvgRevenue
    FROM Sales s
    INNER JOIN Products p ON s.ProductID = p.ProductID
    GROUP BY p.Category;
    

80. Find the Most Popular Store for Each Product

    WITH StoreSales AS (
        SELECT StoreID, ProductID, SUM(SalesAmount) AS TotalSales
        FROM Sales
        GROUP BY StoreID, ProductID
    )
    SELECT ProductID, StoreID, TotalSales
    FROM (
        SELECT ProductID, StoreID, TotalSales,
               ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TotalSales DESC) AS Rank
        FROM StoreSales
    ) AS RankedStores
    WHERE Rank = 1;
    

81. Find the Top 5 Employees by Total Sales Amount

    SELECT TOP 5 EmployeeID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY EmployeeID
    ORDER BY TotalSales DESC;
    

82. Find the Most Common Product in Each Category

    WITH ProductCounts AS (
        SELECT Category, ProductID, COUNT(*) AS PurchaseCount
        FROM Sales
        INNER JOIN Products ON Sales.ProductID = Products.ProductID
        GROUP BY Category, ProductID
    )
    SELECT Category, ProductID, PurchaseCount
    FROM (
        SELECT Category, ProductID, PurchaseCount,
               ROW_NUMBER() OVER (PARTITION BY Category ORDER BY PurchaseCount DESC) AS Rank
        FROM ProductCounts
    ) AS RankedProducts
    WHERE Rank = 1;
    

83. Find the Top 5 Products by Number of Reviews

    SELECT TOP 5 ProductID, COUNT(*) AS NumberOfReviews
    FROM Reviews
    GROUP BY ProductID
    ORDER BY NumberOfReviews DESC;
    

84. Find the Average Time Between Purchases for Each Product

    WITH PurchaseTimes AS (
        SELECT ProductID, PurchaseDate,
               LAG(PurchaseDate, 1) OVER (PARTITION BY ProductID ORDER BY PurchaseDate) AS PreviousPurchaseDate
        FROM Purchases
    )
    SELECT ProductID,
           AVG(DATEDIFF(DAY, PreviousPurchaseDate, PurchaseDate)) AS AvgDaysBetweenPurchases
    FROM PurchaseTimes
    WHERE PreviousPurchaseDate IS NOT NULL
    GROUP BY ProductID;
    

85. Find the Most Recent Product Purchased by Each Customer

    SELECT CustomerID, ProductID, MAX(PurchaseDate) AS MostRecentPurchaseDate
    FROM Purchases
    GROUP BY CustomerID, ProductID;
    

86. Find the Top 5 Products by Average Rating

    SELECT TOP 5 ProductID, AVG(Rating) AS AvgRating
    FROM Reviews
    GROUP BY ProductID
    ORDER BY AvgRating DESC;
    

87. Find the Customers Who Have Purchased the Most Expensive Items

    WITH ExpensivePurchases AS (
        SELECT CustomerID, MAX(SalesAmount) AS MaxPurchaseAmount
        FROM Sales
        GROUP BY CustomerID
    )
    SELECT CustomerID, MaxPurchaseAmount
    FROM ExpensivePurchases
    WHERE MaxPurchaseAmount = (SELECT MAX(MaxPurchaseAmount) FROM ExpensivePurchases);
    

88. Find the Employees with the Most Consecutive Working Days

    WITH ConsecutiveDays AS (
        SELECT EmployeeID, WorkDate,
               ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY WorkDate) - ROW_NUMBER() OVER (ORDER BY WorkDate) AS GroupNum
        FROM WorkHistory
    )
    SELECT EmployeeID, MIN(WorkDate) AS StartDate, MAX(WorkDate) AS EndDate, COUNT(*) AS ConsecutiveDays
    FROM ConsecutiveDays
    GROUP BY EmployeeID, GroupNum
    ORDER BY ConsecutiveDays DESC;
    

89. Find the Total Revenue for Each Region

    SELECT r.Region, SUM(s.SalesAmount) AS TotalRevenue
    FROM Sales s
    INNER JOIN Regions r ON s.RegionID = r.RegionID
    GROUP BY r.Region;
    

90. Find the Products with the Highest Gross Profit

    SELECT ProductID, SUM(Profit) AS TotalGrossProfit
    FROM Sales
    GROUP BY ProductID
    ORDER BY TotalGrossProfit DESC;
    

91. Find the Top 3 Salesperson by Total Sales Amount

    SELECT TOP 3 SalespersonID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY SalespersonID
    ORDER BY TotalSales DESC;
    

92. Find the Products That Have Not Been Sold in the Last 12 Months

    SELECT p.ProductID, p.ProductName
    FROM Products p
    LEFT JOIN Sales s ON p.ProductID = s.ProductID AND s.SaleDate > DATEADD(MONTH, -12, GETDATE())
    WHERE s.ProductID IS NULL;
    

93. Find the Average Time Between Customer Purchases

    WITH PurchaseIntervals AS (
        SELECT CustomerID, PurchaseDate,
               LAG(PurchaseDate, 1) OVER (PARTITION BY CustomerID ORDER BY PurchaseDate) AS PreviousPurchaseDate
        FROM Purchases
    )
    SELECT AVG(DATEDIFF(DAY, PreviousPurchaseDate, PurchaseDate)) AS AvgDaysBetweenPurchases
    FROM PurchaseIntervals
    WHERE PreviousPurchaseDate IS NOT NULL;
    

94. Find the Total Revenue by Product and Year

    SELECT ProductID, YEAR(SaleDate) AS SaleYear, SUM(SalesAmount) AS TotalRevenue
    FROM Sales
    GROUP BY ProductID, YEAR(SaleDate)
    ORDER BY ProductID, SaleYear;
    

95. Find the Most Profitable Product for Each Region

    WITH RegionalProfits AS (
        SELECT r.Region, p.ProductID, SUM(s.Profit) AS TotalProfit
        FROM Sales s
        INNER JOIN Products p ON s.ProductID = p.ProductID
        INNER JOIN Regions r ON s.RegionID = r.RegionID
        GROUP BY r.Region, p.ProductID
    )
    SELECT Region, ProductID, TotalProfit
    FROM (
        SELECT Region, ProductID, TotalProfit,
               ROW_NUMBER() OVER (PARTITION BY Region ORDER BY TotalProfit DESC) AS Rank
        FROM RegionalProfits
    ) AS RankedProducts
    WHERE Rank = 1;
    

96. Find the Total Revenue and Average Revenue per Product

    SELECT ProductID, SUM(SalesAmount) AS TotalRevenue, AVG(SalesAmount) AS AvgRevenue
    FROM Sales
    GROUP BY ProductID;
    

97. Find the Total Number of Sales for Each Region

    SELECT r.Region, COUNT(*) AS NumberOfSales
    FROM Sales s
    INNER JOIN Regions r ON s.RegionID = r.RegionID
    GROUP BY r.Region;
    

98. Find the Top 3 Products by Gross Profit

    SELECT TOP 3 ProductID, SUM(Profit) AS GrossProfit
    FROM Sales
    GROUP BY ProductID
    ORDER BY GrossProfit DESC;
    

99. Find the Average Revenue for Each Region

    
    SELECT r.Region, AVG(s.SalesAmount) AS AvgRevenue
    FROM Sales s
    INNER JOIN Regions r ON s.RegionID = r.RegionID
    GROUP BY r.Region;
    

100. Find the Customers with the Highest Number of Purchases in Each Category
     


     WITH CategoryPurchases AS (

         SELECT 

             c.CustomerID, 

             p.Category, 

             COUNT(*) AS PurchaseCount

         FROM Sales s

         INNER JOIN Products p ON s.ProductID = p.ProductID

         INNER JOIN Customers c ON s.CustomerID = c.CustomerID

         GROUP BY c.CustomerID, p.Category

     )

     SELECT 

         Category, 

         CustomerID, 

         PurchaseCount

     FROM (

         SELECT 

             Category, 

             CustomerID, 

             PurchaseCount,

             ROW_NUMBER() OVER (PARTITION BY Category ORDER BY PurchaseCount DESC) AS Rank

         FROM CategoryPurchases

     ) AS RankedPurchases

     WHERE Rank = 1;