5 frequently Asked SQL Interview Questions with Answers in Data Engineering interviews:
𝐃𝐢𝐟𝐟𝐢𝐜𝐮𝐥𝐭𝐲 - 𝐌𝐞𝐝𝐢𝐮𝐦
⚫️Find the Top 3 Employees with the Highest Total Sales in Each Region
Schema : Using Employees (EmployeeID, Name, RegionID) and Sales (SaleID, EmployeeID, Amount)
WITH RankedSales AS (
SELECT e.EmployeeID,
e.Name,
e.RegionID,
SUM(s.Amount) AS TotalSales,
RANK() OVER (PARTITION BY e.RegionID ORDER BY SUM(s.Amount) DESC) AS SalesRank
FROM Employees e
JOIN Sales s ON e.EmployeeID = s.EmployeeID
GROUP BY e.EmployeeID, e.Name, e.RegionID
)
SELECT EmployeeID, Name, RegionID, TotalSales
FROM RankedSales
WHERE SalesRank <= 3;
⚫️Identify Products with Sales Increasing for Three Consecutive Months
Schema : Sales (ProductID, SaleDate, Quantity).
WITH MonthlySales AS (
SELECT ProductID,
DATE_TRUNC('month', SaleDate) AS Month,
SUM(Quantity) AS MonthlyQuantity
FROM Sales
GROUP BY ProductID, DATE_TRUNC('month', SaleDate)
),
SalesGrowth AS (
SELECT ProductID,
Month,
MonthlyQuantity,
LAG(MonthlyQuantity, 1) OVER (PARTITION BY ProductID ORDER BY Month) AS PrevMonth1,
LAG(MonthlyQuantity, 2) OVER (PARTITION BY ProductID ORDER BY Month) AS PrevMonth2
FROM MonthlySales
)
SELECT ProductID, Month, MonthlyQuantity
FROM SalesGrowth
WHERE MonthlyQuantity > PrevMonth1 AND PrevMonth1 > PrevMonth2;
⚫️List Customers Who Placed Orders in Consecutive Months
Schema : Orders (OrderID, CustomerID, OrderDate)
WITH MonthlyOrders AS (
SELECT CustomerID,
DATE_TRUNC('month', OrderDate) AS OrderMonth
FROM Orders
GROUP BY CustomerID, DATE_TRUNC('month', OrderDate)
),
ConsecutiveMonths AS (
SELECT CustomerID,
OrderMonth,
LAG(OrderMonth, 1) OVER (PARTITION BY CustomerID ORDER BY OrderMonth) AS PrevMonth
FROM MonthlyOrders
)
SELECT DISTINCT CustomerID
FROM ConsecutiveMonths
WHERE OrderMonth = PrevMonth INTERVAL '1 month';
⚫️Find the Second Highest Salary in Each Department
Schema: Employees (EmployeeID, Name, DepartmentID, Salary)
WITH RankedSalaries AS (
SELECT DepartmentID,
Salary,
DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
FROM Employees
)
SELECT DepartmentID, Salary
FROM RankedSalaries
WHERE Rank = 2;
⚫️Calculate the Moving Average of Sales for Each Product Over the Last 3 Months
Schema : Given Sales (SaleID, ProductID, SaleDate, Amount)
SELECT ProductID,
SaleDate,
Amount,
AVG(Amount) OVER (PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Sales;
♻️ I hope you found this useful! If you did, please repost it.
👋🏽 Follow me for more about the Data career.
