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

Please use the same ShopDB database from here.

#1. Identify the top 3 products with the highest average charge amount over the last year.
SELECT TOP 3 p.ProductID, p.ProductName, AVG(c.ChargeAmount) AS AvgChargeAmount
FROM Product p
JOIN Chargeassignment c ON p.ProductID = c.ProductID
WHERE c.ChargeDate >= DATEADD(year, -1, GETDATE())
GROUP BY p.ProductID, p.ProductName
ORDER BY AvgChargeAmount DESC;

 

# 2. Find the customer who has been invoiced for the most distinct products.
SELECT i.CustomerID, COUNT(DISTINCT c.ProductID) AS DistinctProductCount
FROM Invoice i
JOIN Chargeassignment c ON i.InvoiceID = c.InvoiceID
GROUP BY i.CustomerID
ORDER BY DistinctProductCount DESC
LIMIT 1;

 

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

 

4. Determine the average charge amount for each product category, considering only the top 10 most expensive products in each category.
WITH TopExpensiveProducts AS (
SELECT
p.Category,
p.ProductID,
p.ProductName,
p.Price,
ROW_NUMBER() OVER (PARTITION BY p.Category ORDER BY p.Price DESC) AS RowNum
FROM Product p
)
SELECT p.Category, AVG(c.ChargeAmount) AS AvgChargeAmount
FROM TopExpensiveProducts tep
JOIN Chargeassignment c ON tep.ProductID = c.ProductID
WHERE tep.RowNum <= 10
GROUP BY tep.Category;

 

5. Identify the top 5 customers with the highest total invoice amounts over their lifetime.
SELECT TOP 5 i.CustomerID, SUM(i.TotalAmount) AS TotalSpent
FROM Invoice i
GROUP BY i.CustomerID
ORDER BY TotalSpent DESC;

 

6. Calculate the cumulative total revenue for each product category up to each month of the current year.
WITH MonthlyRevenue AS (
SELECT
p.Category,
MONTH(i.InvoiceDate) AS Month,
SUM(c.ChargeAmount) AS MonthlyRevenue
FROM Product p
JOIN Chargeassignment c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE YEAR(i.InvoiceDate) = YEAR(GETDATE())
GROUP BY p.Category, MONTH(i.InvoiceDate)
),
CumulativeRevenue AS (
SELECT
Category,
Month,
MonthlyRevenue,
SUM(MonthlyRevenue) OVER (PARTITION BY Category ORDER BY Month) AS CumulativeRevenue
FROM MonthlyRevenue
)
SELECT Category, Month, CumulativeRevenue
FROM CumulativeRevenue;

 

7. Find the average charge amount for products that have been charged more than once on the same invoice.
WITH MultipleCharges AS (
SELECT ProductID, InvoiceID, COUNT(*) AS ChargeCount
FROM Chargeassignment
GROUP BY ProductID, InvoiceID
HAVING COUNT(*) > 1
)
SELECT AVG(c.ChargeAmount) AS AvgChargeAmount
FROM Chargeassignment c
JOIN MultipleCharges mc ON c.ProductID = mc.ProductID AND c.InvoiceID = mc.InvoiceID;

 

8. List the top 3 months with the highest total charge amounts for each product category in the last 2 years.
WITH MonthlyCharges AS (
SELECT
p.Category,
YEAR(c.ChargeDate) AS Year,
MONTH(c.ChargeDate) AS Month,
SUM(c.ChargeAmount) AS TotalCharges
FROM Product p
JOIN Chargeassignment c ON p.ProductID = c.ProductID
WHERE c.ChargeDate >= DATEADD(year, -2, GETDATE())
GROUP BY p.Category, YEAR(c.ChargeDate), MONTH(c.ChargeDate)
),
TopMonths AS (
SELECT
Category,
Year,
Month,
TotalCharges,
ROW_NUMBER() OVER (PARTITION BY Category ORDER BY TotalCharges DESC) AS RowNum
FROM MonthlyCharges
)
SELECT Category, Year, Month, TotalCharges
FROM TopMonths
WHERE RowNum <= 3;

 

9. Identify invoices where the total charged amount deviates by more than 20% from the invoice total amount.
SELECT i.InvoiceID, i.TotalAmount, SUM(c.ChargeAmount) AS ChargedAmount,
ABS(SUM(c.ChargeAmount) – i.TotalAmount) / i.TotalAmount * 100 AS DeviationPercent
FROM Invoice i
JOIN Chargeassignment c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID, i.TotalAmount
HAVING ABS(SUM(c.ChargeAmount) – i.TotalAmount) / i.TotalAmount * 100 > 20;

 

10. Find products that have been invoiced every month for the past year.
WITH MonthlyInvoices AS (
SELECT p.ProductID, p.ProductName, MONTH(i.InvoiceDate) AS InvoiceMonth
FROM Product p
JOIN Chargeassignment c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE i.InvoiceDate >= DATEADD(year, -1, GETDATE())
GROUP BY p.ProductID, p.ProductName, MONTH(i.InvoiceDate)
)
SELECT ProductID, ProductName
FROM MonthlyInvoices
GROUP BY ProductID, ProductName
HAVING COUNT(DISTINCT InvoiceMonth) = 12;

WITH MonthlyInvoices AS (
SELECT p.ProductID, p.ProductName, MONTH(i.InvoiceDate) AS InvoiceMonth
FROM Product p
JOIN Chargeassignment c ON p.ProductID = c.ProductID
JOIN Invoice i ON c.InvoiceID = i.InvoiceID
WHERE i.InvoiceDate >= DATEADD(year, -1, GETDATE())
GROUP BY p.ProductID, p.ProductName, MONTH(i.InvoiceDate)
)
SELECT ProductID, ProductName
FROM MonthlyInvoices
GROUP BY ProductID, ProductName
HAVING COUNT(DISTINCT InvoiceMonth) = 12;

 

11. Calculate the average and median charge amounts for each product.
WITH ChargeStats AS (
SELECT
p.ProductID,
p.ProductName,
c.ChargeAmount,
ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY c.ChargeAmount) AS RowNum,
COUNT(*) OVER (PARTITION BY p.ProductID) AS TotalCount
FROM Product p
JOIN Chargeassignment c ON p.ProductID = c.ProductID
)
SELECT
ProductID,
ProductName,
AVG(ChargeAmount) AS AvgChargeAmount,
MIN(CASE WHEN RowNum = (TotalCount + 1) / 2 THEN ChargeAmount END) AS MedianChargeAmount
FROM ChargeStats
GROUP BY ProductID, ProductName;

 

12. List customers who have been invoiced for more than $10,000 total but have no single invoice over $1,000.
WITH CustomerTotals AS (
SELECT i.CustomerID, SUM(i.TotalAmount) AS TotalSpent
FROM Invoice i
GROUP BY i.CustomerID
HAVING SUM(i.TotalAmount) > 10000
)
SELECT ct.CustomerID
FROM CustomerTotals ct
JOIN Invoice i ON ct.CustomerID = i.CustomerID
GROUP BY ct.CustomerID
HAVING MAX(i.TotalAmount) <= 1000;

 

13. Identify products that have a variance in charge amounts greater than 50% of their average charge amount.
WITH ChargeStats AS (
SELECT
p.ProductID,
p.ProductName,
AVG(c.ChargeAmount) AS AvgChargeAmount,
VARIANCE(c.ChargeAmount) AS ChargeVariance
FROM Product p
JOIN Chargeassignment c ON p.ProductID = c.ProductID
GROUP BY p.ProductID, p.ProductName
)
SELECT ProductID, ProductName, ChargeVariance
FROM ChargeStats
WHERE ChargeVariance > 0.5 * AvgChargeAmount;

 

14. Find invoices where the sum of charges for each product is greater than the invoice total amount.
SELECT i.InvoiceID, i.TotalAmount, SUM(c.ChargeAmount) AS ChargedAmount
FROM Invoice i
JOIN Chargeassignment c ON i.InvoiceID = c.InvoiceID
GROUP BY i.InvoiceID, i.TotalAmount
HAVING SUM(c.ChargeAmount) > i.TotalAmount;

 

15. Calculate the total revenue and number of charges for products that have not been charged in the last 3 months.
SELECT p.ProductID, p.ProductName,
SUM(c.ChargeAmount) AS TotalRevenue,
COUNT(c.ChargeID) AS ChargeCount
FROM Product p
JOIN Chargeassignment c ON p.ProductID = c.ProductID
WHERE c.ChargeDate < DATEADD(month, -3, GETDATE())
GROUP BY p.ProductID, p.ProductName;