SQL Server Complex queries Frequently asked in interviews

Srikaanth 0

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

Tags

Post a Comment

Post a Comment

Previous Post Next Post