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.

Advanced SQL Coding Scenarios set - 1(1–10)

 These are the types of questions frequently asked for Senior SQL Developer, Tableau Lead, BI Lead, BI Architect, Data Analyst, and Data Warehouse interviews.

---

1. Find Duplicate Records

Question

Find duplicate employee names.

SELECT EmpName,
       COUNT(*)
FROM Employee
GROUP BY EmpName
HAVING COUNT(*) > 1;

---

2. Delete Duplicate Records

Question

Delete duplicates while keeping the first record.

WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER
       (PARTITION BY EmpName ORDER BY EmpID) 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

SELECT Salary
FROM
(
SELECT Salary,
       DENSE_RANK() OVER
       (ORDER BY Salary DESC) rk
FROM Employee
)x
WHERE rk = 3;

---

5. Find Nth Highest Salary

DECLARE @N INT = 5;

SELECT Salary
FROM
(
SELECT Salary,
       DENSE_RANK() OVER
       (ORDER BY Salary DESC) rk
FROM Employee
)x
WHERE rk = @N;

---

6. Highest Salary in Each Department

SELECT *
FROM
(
SELECT *,
       DENSE_RANK() OVER
       (
       PARTITION BY DeptID
       ORDER BY Salary DESC
       ) rk
FROM Employee
)x
WHERE rk = 1;

---

7. Top 3 Salaries in Each Department

SELECT *
FROM
(
SELECT *,
       DENSE_RANK() OVER
       (
       PARTITION BY DeptID
       ORDER BY Salary DESC
       ) rk
FROM Employee
)x
WHERE rk <= 3;

---

8. Employees Earning More Than Manager

SELECT e.EmpName
FROM Employee e
JOIN Employee m
ON e.ManagerID = m.EmpID
WHERE e.Salary > m.Salary;

---

9. Find Employees Without Manager

SELECT *
FROM Employee
WHERE ManagerID IS NULL;

---

10. Find Employees Joined in Last 30 Days

SELECT *
FROM Employee
WHERE JoinDate >= DATEADD(day,-30,GETDATE());

SQL Managerial / Architect Questions

For 10+ years experience.

Q1: How do you design a reporting database?

• Star schema
• Fact tables
• Dimension tables

Q2: How do you handle 100M+ records?

• Partitioning
• Indexing
• Incremental loads

Q3: How do you ensure data quality?

• Validation checks
• Reconciliation
• Audit tables

Q4: How do you troubleshoot dashboard performance?

• Optimize SQL
• Reduce extracts
• Aggregate data

Q5: How do you mentor junior developers?

• Code reviews
• Standards
• Reusable SQL templates

Advanced SQL Scenario-Based Questions (Top Priority)

These are common for 8–15 years experience.

Examples:

Q1: Find consecutive login days for users.

Topics:

• LAG()
• Date calculations
• Gap analysis

Q2: Find customers who purchased in 3 consecutive months.

Topics:

• Window functions
• Date logic

Q3: Detect missing invoice numbers.

Topics:

• LEAD()
• Sequence analysis

Q4: Find longest employee tenure in each department.

Topics:

• DENSE_RANK()
• Date functions

Q5: Find products contributing to 80% of revenue.

Topics:

• Running totals
• Pareto analysis