Advanced SQL Coding Scenarios set - 4(34–50)

 

34. Calculate Revenue Contribution %

SELECT CustomerID,
SUM(Amount)*100.0/
SUM(SUM(Amount)) OVER()
ContributionPct
FROM Orders
GROUP BY CustomerID;

---

35. Find Customers Contributing 80% Revenue

Uses:

SUM() OVER()

Running percentage logic.

(Pareto Analysis)

---

36. Find Latest Salary for Employee

SELECT *
FROM
(
SELECT *,
ROW_NUMBER() OVER
(
PARTITION BY EmpID
ORDER BY EffectiveDate DESC
) rn
FROM SalaryHistory
)x
WHERE rn=1;

---

37. Find Employees With Salary Increase

SELECT *,
Salary -
LAG(Salary)
OVER(PARTITION BY EmpID
ORDER BY EffectiveDate)
IncreaseAmt
FROM SalaryHistory;

---

38. Find Employees With No Salary Increase

WHERE IncreaseAmt <= 0

---

39. Find Longest Serving Employee

SELECT TOP 1 *
FROM Employee
ORDER BY JoinDate;

---

40. Find Employee Tenure

SELECT EmpName,
DATEDIFF(YEAR,JoinDate,GETDATE())
Tenure
FROM Employee;

---

41. Find Orders Placed Today

SELECT *
FROM Orders
WHERE CAST(OrderDate AS DATE)=CAST(GETDATE() AS DATE);

---

42. Find Orders Last 7 Days

SELECT *
FROM Orders
WHERE OrderDate >= DATEADD(day,-7,GETDATE());

---

43. Find Sales by Month

SELECT YEAR(OrderDate),
       MONTH(OrderDate),
       SUM(Amount)
FROM Orders
GROUP BY YEAR(OrderDate),
         MONTH(OrderDate);

---

44. Pivot Monthly Sales

SELECT *
FROM Sales
PIVOT
(
SUM(Amount)
FOR Month IN
([Jan],[Feb],[Mar])
)p;

---

45. Unpivot Columns Into Rows

SELECT *
FROM Sales
UNPIVOT
(
Amount
FOR Month IN
(Jan,Feb,Mar)
)u;

---

46. Find Null Values Count

SELECT COUNT(*) -
COUNT(Email)
FROM Customers;

---

47. Find Orphan Records

SELECT *
FROM Child c
LEFT JOIN Parent p
ON c.ParentID=p.ParentID
WHERE p.ParentID IS NULL;

---

48. Compare Source vs Target Counts

SELECT COUNT(*)
FROM SourceTable;

SELECT COUNT(*)
FROM TargetTable;

---

49. Find Changed Records During ETL

SELECT *
FROM Source s
JOIN Target t
ON s.ID=t.ID
WHERE s.HashValue<>t.HashValue;

---

50. Detect Slowly Changing Dimension Type 2 Changes

SELECT *
FROM Source s
JOIN DimCustomer d
ON s.CustomerID=d.CustomerID
WHERE s.Address<>d.Address;

Action:

• Expire current record
• Insert new version

---

Most Frequently Asked Among These

1. 2nd Highest Salary
2. Top N per Department
3. Running Total
4. MoM Growth using LAG
5. Latest Record per Customer
6. Duplicate Detection
7. Employees Above Department Average
8. Customers Without Orders
9. Revenue Contribution %
10. Gap and Island Problems (Missing IDs, Consecutive Dates)

These 10 patterns appear repeatedly across Tableau, Power BI, Snowflake, SQL Server, Oracle, and Data Warehouse interviews.