1. Find Duplicate Records
SELECT Name,
COUNT(*)
FROM Employee
GROUP BY Name
HAVING COUNT(*) > 1;
2. Delete Duplicate Records
WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER
(
PARTITION BY Name
ORDER BY ID
) rn
FROM Employee
)
DELETE FROM CTE
WHERE rn > 1;
3. Find 2nd Highest Salary
SELECT Salary
FROM
(
SELECT Salary,
DENSE_RANK() OVER
(ORDER BY Salary DESC) rk
FROM Employee
)x
WHERE rk=2;
4. Find 3rd Highest Salary
Change rk=2 to:
WHERE rk=3
5. Latest Order Per Customer
SELECT *
FROM
(
SELECT *,
ROW_NUMBER() OVER
(
PARTITION BY CustomerID
ORDER BY OrderDate DESC
) rn
FROM Orders
)x
WHERE rn=1;
6. Employees Earning Above Department Average
SELECT *
FROM Employee e
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
WHERE DeptID=e.DeptID
);
7. Customers With No Orders
SELECT c.*
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID=o.CustomerID
WHERE o.CustomerID IS NULL;
8. Running Total
SUM(Sales) OVER(ORDER BY Date)
9. Month-over-Month Growth
LAG(Sales) OVER(ORDER BY Month)
10. Top 3 Salaries Per Department
DENSE_RANK()
OVER(PARTITION BY DeptID
ORDER BY Salary DESC)
For a Tableau/BI Architect interview, these 10 patterns alone cover roughly 70–80% of SQL coding questions asked in analytics, reporting, data warehousing, and BI projects.
