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

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;

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;

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;

Find Employees with Duplicate Names in Different Departments

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

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;

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);

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;

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;

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;

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;

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);

Get the Most Recent Purchase for Each Customer

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

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;

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;

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;

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;

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;

Find the Most Frequent Product Category

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

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

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

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;

Find Employees Who Have Worked in Multiple Departments

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

Get the Average Salary for Each Manager

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

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;

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;

Get the Date of the First Sale for Each Product

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

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;

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;

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;

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;

Calculate the Cumulative Sales for Each Product

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

Find the Most Common Purchase Date

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

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);

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;

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;

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);

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;

List Employees with the Most Direct Reports

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

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;

Get the Most Expensive Item Purchased by Each Customer

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

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;

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;

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;

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;

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;

Calculate the Total Revenue Generated by Each Salesperson

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

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);

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;

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;

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;

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;

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;

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);

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;

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;

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;

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);

Find the Most Recent Transaction for Each Account

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

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;

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;

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;

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;

Find the Most Expensive Purchase for Each Customer

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

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;

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;

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;

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;

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;

Find the Most Frequent Sales Date

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

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;

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;

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;

Find the Most Recent Review for Each Product

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

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;

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;

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);

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;

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;

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;

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;

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;

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;

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;

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;

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;

Find the Most Recent Product Purchased by Each Customer

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

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;

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);

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;

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;

Find the Products with the Highest Gross Profit

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

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;

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;

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;

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;

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;

Find the Total Revenue and Average Revenue per Product

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

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;

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;

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;

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;

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

MS SQL Server Query programs interview questions and answers freshers experienced advanced level software IT Jobs written test quires tutorial pdf

August 13, 2024

Sql Server Complex queries asking in interviews

Complex Queries

Get Free Gifts

Most Viewed

Popular Posts

Blog Archive

Labels

Contact Us

Millet

Interview QA

Information

Small Business Ideas