SQL Server Complex queries Frequently asked in interviews

Here’s a list of complex SQL Server queries that might be asked in interviews. These queries cover advanced topics such as window functions, complex joins, subqueries, CTEs, and more.

Complex Queries

1. Find Employees with a Salary Above the Average in Their Department
   
   

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

2. Find the Top 3 Highest Salaries per Department
   
   

   WITH RankedSalaries AS (
       SELECT EmployeeID, DepartmentID, Salary,
              ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
       FROM Employees
   )
   SELECT * FROM RankedSalaries
   WHERE Rank <= 3;
   
   

3. Find Employees with More Than One Manager
   
   

   SELECT EmployeeID, COUNT(DISTINCT ManagerID) AS ManagerCount
   FROM Employees
   GROUP BY EmployeeID
   HAVING COUNT(DISTINCT ManagerID) > 1;
   
   

4. Get the Running Total of Sales for Each Month
   
   

   SELECT OrderDate, SalesAmount,
          SUM(SalesAmount) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS RunningTotal
   FROM Sales;
   
   

5. Find the Second Highest Salary in the Company
   
   

   SELECT MAX(Salary) AS SecondHighestSalary
   FROM Employees
   WHERE Salary < (SELECT MAX(Salary) FROM Employees);
   
   

6. Find All Employees Who Joined in the Same Year as Their Manager
   
   

   SELECT e.EmployeeID, e.FirstName, e.JoinDate, m.FirstName AS ManagerName
   FROM Employees e
   INNER JOIN Employees m ON e.ManagerID = m.EmployeeID
   WHERE YEAR(e.JoinDate) = YEAR(m.JoinDate);
   
   

7. Find the Most Recent Order for Each Customer
   
   

   WITH RecentOrders AS (
       SELECT CustomerID, OrderID, OrderDate,
              ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
       FROM Orders
   )
   SELECT CustomerID, OrderID, OrderDate
   FROM RecentOrders
   WHERE rn = 1;
   
   

8. Calculate the Percentage of Total Sales for Each Product
   
   

   SELECT ProductID, ProductName, SalesAmount,
          (SalesAmount * 100.0 / SUM(SalesAmount) OVER ()) AS SalesPercentage
   FROM Sales;
   
   

9. Find All Customers Who Made Purchases in All Available Product Categories
   
   

   SELECT CustomerID
   FROM Orders o
   INNER JOIN Products p ON o.ProductID = p.ProductID
   GROUP BY CustomerID
   HAVING COUNT(DISTINCT p.CategoryID) = (SELECT COUNT(DISTINCT CategoryID) FROM Products);
   
   

10. Find Employees Whose Salary is Above the Average Salary of Their Department
    
    

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

11. Find the Largest Gap Between Consecutive Orders for Each Customer

    WITH OrderGaps AS (
        SELECT CustomerID, OrderID, OrderDate,
               LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PrevOrderDate
        FROM Orders
    )
    SELECT CustomerID, MAX(DATEDIFF(DAY, PrevOrderDate, OrderDate)) AS MaxGap
    FROM OrderGaps
    GROUP BY CustomerID;
    
    

12. Get the Most Common Purchase Quantity

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

13. Get the Median Salary of Employees
    
    

    WITH Salaries AS (
        SELECT Salary,
               ROW_NUMBER() OVER (ORDER BY Salary) AS RowAsc,
               ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowDesc
        FROM Employees
    )
    SELECT AVG(Salary) AS MedianSalary
    FROM Salaries
    WHERE RowAsc = RowDesc
       OR RowAsc + 1 = RowDesc
       OR RowAsc = RowDesc + 1;
    
    

14. Find Products with Sales Above the Average Sales of All Products
    
    

    WITH AvgSales AS (
        SELECT AVG(SalesAmount) AS AvgSalesAmount
        FROM Sales
    )
    SELECT p.ProductID, p.ProductName, s.SalesAmount
    FROM Products p
    INNER JOIN Sales s ON p.ProductID = s.ProductID
    CROSS JOIN AvgSales a
    WHERE s.SalesAmount > a.AvgSalesAmount;
    

15. Find Customers with Multiple Orders on the Same Day

    SELECT CustomerID, OrderDate, COUNT(*) AS OrderCount
    FROM Orders
    GROUP BY CustomerID, OrderDate
    HAVING COUNT(*) > 1;
    

16. Identify Products that Have Never Been Ordered

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

17. Calculate Year-over-Year Growth for Each Product

    WITH YearlySales AS (
        SELECT ProductID, YEAR(OrderDate) AS Year, SUM(SalesAmount) AS TotalSales
        FROM Orders
        GROUP BY ProductID, YEAR(OrderDate)
    )
    SELECT a.ProductID, a.Year AS Year, a.TotalSales AS CurrentYearSales,
           b.TotalSales AS PreviousYearSales,
           (a.TotalSales - b.TotalSales) * 100.0 / b.TotalSales AS YearOverYearGrowth
    FROM YearlySales a
    LEFT JOIN YearlySales b ON a.ProductID = b.ProductID AND a.Year = b.Year + 1
    WHERE b.TotalSales IS NOT NULL;
    

18. Find Customers Who Have Ordered Every Product

    SELECT CustomerID
    FROM Orders o
    GROUP BY CustomerID
    HAVING COUNT(DISTINCT o.ProductID) = (SELECT COUNT(*) FROM Products);
    

19. Find Employees Who Are Not in the Same Department as Their Manager

    SELECT e.EmployeeID, e.FirstName, m.FirstName AS ManagerName
    FROM Employees e
    INNER JOIN Employees m ON e.ManagerID = m.EmployeeID
    WHERE e.DepartmentID <> m.DepartmentID;
    

20. Retrieve the Top N Rows for Each Category

    WITH RankedProducts AS (
        SELECT ProductID, CategoryID, ProductName, SalesAmount,
               ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY SalesAmount DESC) AS Rank
        FROM Products
    )
    SELECT * FROM RankedProducts
    WHERE Rank <= 5;
    

21. Get the Number of Orders Placed Each Day of the Week

    SELECT DATENAME(WEEKDAY, OrderDate) AS DayOfWeek, COUNT(*) AS OrderCount
    FROM Orders
    GROUP BY DATENAME(WEEKDAY, OrderDate)
    ORDER BY DATEPART(WEEKDAY, OrderDate);
    

22. Find Employees Who Have Had the Same Job Title for More Than 5 Years

    SELECT EmployeeID, JobTitle, MIN(EffectiveDate) AS FirstDate, GETDATE() AS CurrentDate
    FROM JobHistory
    GROUP BY EmployeeID, JobTitle
    HAVING DATEDIFF(YEAR, MIN(EffectiveDate), GETDATE()) > 5;
    

23. Get the Total Sales for Each Product by Month

    SELECT ProductID, YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month,
           SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID, YEAR(OrderDate), MONTH(OrderDate);
    

24. Find the Average Order Value by Customer

    SELECT CustomerID, AVG(OrderValue) AS AvgOrderValue
    FROM Orders
    GROUP BY CustomerID;
    

25. Find the Employee with the Longest Continuous Employment Period

    WITH EmploymentPeriods AS (
        SELECT EmployeeID, MIN(JoinDate) AS StartDate, MAX(LeaveDate) AS EndDate
        FROM EmploymentHistory
        GROUP BY EmployeeID
    )
    SELECT EmployeeID, DATEDIFF(DAY, StartDate, EndDate) AS EmploymentDuration
    FROM EmploymentPeriods
    ORDER BY EmploymentDuration DESC
    OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;
    

26. Find the Products that Have Been Ordered in Every Month of the Current Year

    SELECT ProductID
    FROM Orders
    WHERE YEAR(OrderDate) = YEAR(GETDATE())
    GROUP BY ProductID
    HAVING COUNT(DISTINCT MONTH(OrderDate)) = 12;
    

27. Get the Total Sales and Total Orders for Each Customer

    SELECT CustomerID, SUM(SalesAmount) AS TotalSales, COUNT(*) AS TotalOrders
    FROM Orders
    GROUP BY CustomerID;
    

28. Find Customers with Orders in Both the First and Last Quarters of the Year

    SELECT CustomerID
    FROM Orders
    WHERE QUARTER(OrderDate) IN (1, 4)
    GROUP BY CustomerID
    HAVING COUNT(DISTINCT QUARTER(OrderDate)) = 2;
    

29. Retrieve Orders with the Largest Number of Items

    SELECT OrderID, SUM(Quantity) AS TotalItems
    FROM OrderDetails
    GROUP BY OrderID
    ORDER BY TotalItems DESC
    OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;
    

30. Find the Customer with the Highest Total Order Value

    SELECT TOP 1 CustomerID, SUM(OrderValue) AS TotalOrderValue
    FROM Orders
    GROUP BY CustomerID
    ORDER BY TotalOrderValue DESC;
    

31. Find the Average Length of Time Between Order Placement and Delivery

    SELECT AVG(DATEDIFF(DAY, OrderDate, DeliveryDate)) AS AvgDeliveryTime
    FROM Orders
    WHERE DeliveryDate IS NOT NULL;
    

32. Find Employees with the Highest Performance Rating in Each Department

    WITH DeptRatings AS (
        SELECT DepartmentID, EmployeeID, PerformanceRating,
               ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY PerformanceRating DESC) AS Rank
        FROM EmployeePerformance
    )
    SELECT * FROM DeptRatings
    WHERE Rank = 1;
    

33. Find Products with Sales that Increased Year-over-Year

    WITH YearlySales AS (
        SELECT ProductID, YEAR(OrderDate) AS Year, SUM(SalesAmount) AS TotalSales
        FROM Sales
        GROUP BY ProductID, YEAR(OrderDate)
    )
    SELECT a.ProductID, a.Year AS CurrentYear, a.TotalSales AS CurrentYearSales,
           b.TotalSales AS PreviousYearSales
    FROM YearlySales a
    INNER JOIN YearlySales b ON a.ProductID = b.ProductID AND a.Year = b.Year + 1
    WHERE a.TotalSales > b.TotalSales;
    

34. Retrieve the Products with the Most Frequent Orders

    SELECT ProductID, COUNT(*) AS OrderCount
    FROM OrderDetails
    GROUP BY ProductID
    ORDER BY OrderCount DESC;
    

35. Find the Top 5 Customers by Total Purchase Amount

    SELECT TOP 5 CustomerID, SUM(OrderValue) AS TotalPurchaseAmount
    FROM Orders
    GROUP BY CustomerID
    ORDER BY TotalPurchaseAmount DESC;
    

36. Find the Longest Gap Between Consecutive Orders for Each Customer

    WITH OrderGaps AS (
        SELECT CustomerID, OrderDate,
               LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PrevOrderDate
        FROM Orders
    )
    SELECT CustomerID, MAX(DATEDIFF(DAY, PrevOrderDate, OrderDate)) AS LongestGap
    FROM OrderGaps
    GROUP BY CustomerID;
    

37. Get the Average Order Value for Each Product Category

    SELECT c.CategoryID, AVG(o.OrderValue) AS AvgOrderValue
    FROM Orders o
    INNER JOIN Products p ON o.ProductID = p.ProductID
    INNER JOIN Categories c ON p.CategoryID = c.CategoryID
    GROUP BY c.CategoryID;
    

38. Retrieve Orders with the Longest Delivery Time

    SELECT OrderID, DATEDIFF(DAY, OrderDate, DeliveryDate) AS DeliveryTime
    FROM Orders
    WHERE DeliveryDate IS NOT NULL
    ORDER BY DeliveryTime DESC;
    

39. Find Employees Who Have Never Missed a Day of Work

    SELECT EmployeeID
    FROM Attendance
    GROUP BY EmployeeID
    HAVING COUNT(*) = (SELECT COUNT(DISTINCT WorkDate) FROM Calendar);
    

40. Find the Product 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
    OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;
    

41. Calculate the Moving Average of Sales Over the Last 3 Months

    SELECT OrderDate, SalesAmount,
           AVG(SalesAmount) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
    FROM Sales;
    

42. Find the Top 10 Products with the Most Returns
    
    

    SELECT TOP 10 ProductID, COUNT(*) AS ReturnCount
    FROM Returns
    GROUP BY ProductID
    ORDER BY ReturnCount DESC;
    
    

43. Find the Top 3 Products with the Highest Total Sales in the Last Year
    
    

    SELECT TOP 3 p.ProductID, p.ProductName, SUM(o.SalesAmount) AS TotalSales
    FROM Orders o
    INNER JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = YEAR(GETDATE()) - 1
    GROUP BY p.ProductID, p.ProductName
    ORDER BY TotalSales DESC;
    
    

44. Get the Month with the Highest Sales for Each Product
    
    

    WITH MonthlySales AS (
        SELECT ProductID, YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
        FROM Orders
        GROUP BY ProductID, YEAR(OrderDate), MONTH(OrderDate)
    )
    SELECT ProductID, Year, Month, TotalSales
    FROM MonthlySales
    WHERE (ProductID, TotalSales) IN (
        SELECT ProductID, MAX(TotalSales)
        FROM MonthlySales
        GROUP BY ProductID
    );
    
    

45. Find the Most Expensive Product Purchased by Each Customer
    
    

    WITH MaxPurchase AS (
        SELECT o.CustomerID, o.OrderID, o.ProductID, o.Quantity, p.Price,
               ROW_NUMBER() OVER (PARTITION BY o.CustomerID ORDER BY p.Price * o.Quantity DESC) AS Rank
        FROM Orders o
        INNER JOIN Products p ON o.ProductID = p.ProductID
    )
    SELECT CustomerID, OrderID, ProductID, Quantity, Price
    FROM MaxPurchase
    WHERE Rank = 1;
    
    

46. Find the Average Delivery Time by Product Category
    
    

    SELECT c.CategoryID, AVG(DATEDIFF(DAY, o.OrderDate, o.DeliveryDate)) AS AvgDeliveryTime
    FROM Orders o
    INNER JOIN Products p ON o.ProductID = p.ProductID
    INNER JOIN Categories c ON p.CategoryID = c.CategoryID
    WHERE o.DeliveryDate IS NOT NULL
    GROUP BY c.CategoryID;
    
    

47. Get the Highest and Lowest Sale Amount for Each Product
    
    

    SELECT ProductID, MAX(SalesAmount) AS MaxSale, MIN(SalesAmount) AS MinSale
    FROM Sales
    GROUP BY ProductID;
    
    

48. Find Employees Who Have Worked in More Than One Department
    
    

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

49. Get the Number of Products Sold Each Month for the Last Year
    
    

    SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, COUNT(*) AS ProductCount
    FROM Orders
    WHERE OrderDate >= DATEADD(YEAR, -1, GETDATE())
    GROUP BY YEAR(OrderDate), MONTH(OrderDate);
    
    

50. Find the Product with the Highest Sales in Each Region
    
    

    WITH RegionalSales AS (
        SELECT r.RegionID, o.ProductID, SUM(o.SalesAmount) AS TotalSales,
               ROW_NUMBER() OVER (PARTITION BY r.RegionID ORDER BY SUM(o.SalesAmount) DESC) AS Rank
        FROM Orders o
        INNER JOIN Regions r ON o.RegionID = r.RegionID
        GROUP BY r.RegionID, o.ProductID
    )
    SELECT RegionID, ProductID, TotalSales
    FROM RegionalSales
    WHERE Rank = 1;
    
    

51. Find the Average Purchase Frequency per Customer
    
    

    WITH PurchaseCounts AS (
        SELECT CustomerID, COUNT(*) AS PurchaseCount
        FROM Orders
        GROUP BY CustomerID
    )
    SELECT AVG(PurchaseCount) AS AvgPurchaseFrequency
    FROM PurchaseCounts;
    
    

52. Find the Product with the Highest Return Rate
    
    

    WITH ProductReturns AS (
        SELECT ProductID, COUNT(*) AS ReturnCount,
               (SELECT COUNT(*) FROM Orders WHERE ProductID = r.ProductID) AS TotalOrders
        FROM Returns r
        GROUP BY ProductID
    )
    SELECT ProductID, (ReturnCount * 100.0 / TotalOrders) AS ReturnRate
    FROM ProductReturns
    ORDER BY ReturnRate DESC
    OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY

https://mytecbooks.blogspot.com/2024/08/sql-server-complex-queries-frequently.html

SQL Server Complex queries Frequently asked in interviews MS SQL Server Query programs interview questions and answers freshers experienced advanced