SQL Server interview questions and answers for freshers – Set-3

Please use the same ShopDB database from here.

1. Find the total revenue generated by each product category.
SELECT p.Category, SUM(c.ChargeAmount) AS TotalRevenue
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.Category;

2. Identify the products with the highest and lowest average charge amounts.
WITH ProductCharges AS (
SELECT p.ProductID, p.ProductName, AVG(c.ChargeAmount) AS AverageChargeAmount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
)
SELECT TOP 1 * FROM ProductCharges ORDER BY AverageChargeAmount DESC;
SELECT TOP 1 * FROM ProductCharges ORDER BY AverageChargeAmount ASC;

3. Calculate the month-over-month revenue growth for the current year.
WITH MonthlyRevenue AS (
SELECT MONTH(i.InvoiceDate) AS Month, SUM(c.ChargeAmount) AS Revenue
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY MONTH(i.InvoiceDate)
)
SELECT Month, Revenue,
LAG(Revenue) OVER (ORDER BY Month) AS PreviousMonthRevenue,
(Revenue – LAG(Revenue) OVER (ORDER BY Month)) / LAG(Revenue) OVER (ORDER BY Month) * 100 AS GrowthRate
FROM MonthlyRevenue;

4. Find the second highest total charge amount among all invoices.
SELECT DISTINCT TotalChargeAmount
FROM (
SELECT i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
) AS InvoiceTotals
ORDER BY TotalChargeAmount DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROW ONLY;

5. Retrieve the customer who has generated the highest total revenue in the last year.
SELECT TOP 1 i.CustomerID, SUM(c.ChargeAmount) AS TotalRevenue
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
WHERE i.InvoiceDate >= DATEADD(year, -1, GETDATE())
GROUP BY i.CustomerID
ORDER BY TotalRevenue DESC;

6. List the top 5 products with the most significant revenue increase compared to the previous month.
WITH MonthlyProductRevenue AS (
SELECT p.ProductID, p.ProductName, MONTH(i.InvoiceDate) AS Month, SUM(c.ChargeAmount) AS Revenue
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY p.ProductID, p.ProductName, MONTH(i.InvoiceDate)
),
RevenueGrowth AS (
SELECT ProductID, ProductName, Month, Revenue,
LAG(Revenue) OVER (PARTITION BY ProductID ORDER BY Month) AS PreviousMonthRevenue,
Revenue – LAG(Revenue) OVER (PARTITION BY ProductID ORDER BY Month) AS RevenueIncrease
FROM MonthlyProductRevenue
)
SELECT TOP 5 ProductID, ProductName, RevenueIncrease
FROM RevenueGrowth
WHERE RevenueIncrease IS NOT NULL
ORDER BY RevenueIncrease DESC;

7. Find the average charge amount for each product category.
SELECT p.Category, AVG(c.ChargeAmount) AS AverageChargeAmount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.Category;

8. Identify the top 3 customers with the most invoices issued.
SELECT TOP 3 i.CustomerID, COUNT(i.InvoiceID) AS InvoiceCount
FROM Invoice i
GROUP BY i.CustomerID
ORDER BY InvoiceCount DESC;

9. List the products that have had charges assigned in every month of the current year.
WITH MonthlyCharges AS (
SELECT p.ProductID, p.ProductName, MONTH(c.ChargeDate) AS ChargeMonth
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
WHERE YEAR(c.ChargeDate) = YEAR(GETDATE())
GROUP BY p.ProductID, p.ProductName, MONTH(c.ChargeDate)
)
SELECT ProductID, ProductName
FROM MonthlyCharges
GROUP BY ProductID, ProductName
HAVING COUNT(DISTINCT ChargeMonth) = 12;

10. Retrieve the total number of distinct products invoiced each month for the current year.
SELECT MONTH(i.InvoiceDate) AS Month, COUNT(DISTINCT c.ProductID) AS DistinctProducts
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY MONTH(i.InvoiceDate);

11. Find the product with the most consistent monthly revenue (lowest standard deviation) for the current year.
WITH MonthlyProductRevenue AS (
SELECT p.ProductID, p.ProductName, MONTH(i.InvoiceDate) AS Month, SUM(c.ChargeAmount) AS Revenue
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY p.ProductID, p.ProductName, MONTH(i.InvoiceDate)
),
RevenueStats AS (
SELECT ProductID, ProductName, STDEV(Revenue) AS RevenueStdDev
FROM MonthlyProductRevenue
GROUP BY ProductID, ProductName
)
SELECT TOP 1 ProductID, ProductName, RevenueStdDev
FROM RevenueStats
ORDER BY RevenueStdDev ASC;

12. List the invoices that include charges from at least 3 different products.
SELECT i.InvoiceID
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
HAVING COUNT(DISTINCT c.ProductID) >= 3;

13. Find the total revenue generated by each product in the last quarter.
SELECT p.ProductID, p.ProductName, SUM(c.ChargeAmount) AS TotalRevenue
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE i.InvoiceDate >= DATEADD(quarter, -1, GETDATE())
GROUP BY p.ProductID, p.ProductName;

14. Calculate the cumulative revenue for each customer for the current year.
SELECT i.CustomerID, i.InvoiceDate, SUM(c.ChargeAmount) OVER (PARTITION BY i.CustomerID ORDER BY i.InvoiceDate) AS CumulativeRevenue
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE());

15. Identify the invoices with a total charge amount within the top 10% of all invoices.
WITH InvoiceTotals AS (
SELECT i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
),
PercentileThreshold AS (
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY TotalChargeAmount) AS ChargeAmount90
FROM InvoiceTotals
)
SELECT it.InvoiceID, it.TotalChargeAmount
FROM InvoiceTotals it, PercentileThreshold pt
WHERE it.TotalChargeAmount >= pt.ChargeAmount90;

16. Retrieve the details of invoices where the total charge amount is higher than the total revenue generated by a specific product (e.g., ProductID = 7).
WITH ProductRevenue AS (
SELECT ProductID, SUM(c.ChargeAmount) AS TotalRevenue
FROM Charge c
WHERE ProductID = 7
GROUP BY ProductID
)
SELECT i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
HAVING SUM(c.ChargeAmount) > (SELECT TotalRevenue FROM ProductRevenue);

17. List the products with at least 5 charges and calculate the average charge amount for these products.
SELECT p.ProductID, p.ProductName, AVG(c.ChargeAmount) AS AverageChargeAmount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING COUNT(c.ChargeID) >= 5;

18. Find the invoices issued in the current year that include charges for products from at least 3 different categories.
WITH InvoiceCategories AS (
SELECT i.InvoiceID, COUNT(DISTINCT p.Category) AS CategoryCount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
JOIN Product p ON c.ProductID = p.ProductID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY i.InvoiceID
)
SELECT InvoiceID
FROM InvoiceCategories
WHERE CategoryCount >= 3;

19. Calculate the total revenue and the number of charges assigned for each product category.
SELECT p.Category, SUM(c.ChargeAmount) AS TotalRevenue, COUNT(c.ChargeID) AS ChargeCount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.Category;

20. Identify the products that have never been charged more than a specified amount (e.g., $100).
SELECT p.ProductID, p.ProductName
FROM Product p
LEFT JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING MAX(c.ChargeAmount) <= 100 OR MAX(c.ChargeAmount) IS NULL;

21. List the customers and the total number of distinct products they have been invoiced for.
SELECT i.CustomerID, COUNT(DISTINCT c.ProductID) AS DistinctProductCount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.CustomerID;

22. Find the products that have been invoiced more than 5 times in the current year.
SELECT p.ProductID, p.ProductName, COUNT(DISTINCT i.InvoiceID) AS InvoiceCount
FROM Product p
JOIN Chargea c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY p.ProductID, p.ProductName
HAVING COUNT(DISTINCT i.InvoiceID) > 5;

23. Calculate the total charge amount for each invoice and identify the invoice with the maximum total charge amount.
WITH InvoiceTotals AS (
SELECT i.InvoiceID, SUM(c.ChargeAmount) AS TotalChargeAmount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID
)
SELECT TOP 1 InvoiceID, TotalChargeAmount
FROM InvoiceTotals
ORDER BY TotalChargeAmount DESC;

24. Find the products that have been charged in every quarter of the current year.
WITH QuarterlyCharges AS (
SELECT p.ProductID, p.ProductName, DATEPART(quarter, c.ChargeDate) AS ChargeQuarter
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
WHERE YEAR(c.ChargeDate) = YEAR(GETDATE())
GROUP BY p.ProductID, p.ProductName, DATEPART(quarter, c.ChargeDate)
)
SELECT ProductID, ProductName
FROM QuarterlyCharges
GROUP BY ProductID, ProductName
HAVING COUNT(DISTINCT ChargeQuarter) = 4;

25. List the customers who have not been invoiced in the last 6 months.
SELECT DISTINCT i.CustomerID
FROM Invoice i
WHERE i.CustomerID NOT IN (
SELECT DISTINCT CustomerID
FROM Invoice
WHERE InvoiceDate >= DATEADD(month, -6, GETDATE())
);

26. Calculate the total revenue for each product, and find the product that has contributed the most to the overall revenue.
WITH ProductRevenue AS (
SELECT p.ProductID, p.ProductName, SUM(c.ChargeAmount) AS TotalRevenue
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
)
SELECT TOP 1 ProductID, ProductName, TotalRevenue
FROM ProductRevenue
ORDER BY TotalRevenue DESC;

27. Identify the products that have had at least one charge in every month of the previous year.
WITH MonthlyCharges AS (
SELECT p.ProductID, p.ProductName, MONTH(c.ChargeDate) AS ChargeMonth
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
WHERE YEAR(c.ChargeDate) = YEAR(DATEADD(year, -1, GETDATE()))
GROUP BY p.ProductID, p.ProductName, MONTH(c.ChargeDate)
)
SELECT ProductID, ProductName
FROM MonthlyCharges
GROUP BY ProductID, ProductName
HAVING COUNT(DISTINCT ChargeMonth) = 12;

28. Find the total revenue generated by each product category in the last 3 months.
SELECT p.Category, SUM(c.ChargeAmount) AS TotalRevenue
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE i.InvoiceDate >= DATEADD(month, -3, GETDATE())
GROUP BY p.Category;

29. List the invoices that include charges from products of at least 2 different categories.
WITH InvoiceCategories AS (
SELECT i.InvoiceID, COUNT(DISTINCT p.Category) AS CategoryCount
FROM Invoice i
JOIN Charge c ON i.InvoiceID = c.InvoiceID
JOIN Product p ON c.ProductID = p.ProductID
GROUP BY i.InvoiceID
)
SELECT InvoiceID
FROM InvoiceCategories
WHERE CategoryCount >= 2;

30. Calculate the total revenue, average charge amount, and number of charges for each product in the last 6 months.
SELECT p.ProductID, p.ProductName,
SUM(c.ChargeAmount) AS TotalRevenue,
AVG(c.ChargeAmount) AS AverageChargeAmount,
COUNT(c.ChargeID) AS ChargeCount
FROM Product p
JOIN Charge c ON p.ProductID = c.ProductID
WHERE c.ChargeDate >= DATEADD(month, -6, GETDATE())
GROUP BY p.ProductID, p.ProductName;