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

SQL Interview Cheat Sheet - WINDOW FUNCTION CHEAT SHEET

 Most important topic for interviews today.

---

ROW_NUMBER()

Assigns unique numbers.

SELECT *,
ROW_NUMBER() OVER
(
ORDER BY Salary DESC
) rn
FROM Employee;

Result

Name	Salary	RN
A	90000	1
B	90000	2
C	80000	3

---

RANK()

Same rank for ties, skips numbers.

SELECT *,
RANK() OVER
(
ORDER BY Salary DESC
) rk
FROM Employee;

Result

Salary	Rank
90000	1
90000	1
80000	3

---

DENSE_RANK()

No skipped ranks.

SELECT *,
DENSE_RANK() OVER
(
ORDER BY Salary DESC
) rk
FROM Employee;

Result

Salary	Rank
90000	1
90000	1
80000	2

---

PARTITION BY

Creates groups.

SELECT *,
RANK() OVER
(
PARTITION BY DeptID
ORDER BY Salary DESC
) rk
FROM Employee;

Used for:

• Top N per department
• Highest salary per department
• Latest order per customer

---

Running Total

SELECT OrderDate,
       Sales,
       SUM(Sales) OVER
       (
       ORDER BY OrderDate
       ) RunningTotal
FROM Sales;

---

LAG()

Previous row.

SELECT Month,
       Sales,
       LAG(Sales) OVER
       (
       ORDER BY Month
       ) PreviousMonth
FROM Sales;

Used for:

• Month-over-month growth
• Previous salary
• Previous order

---

LEAD()

Next row.

SELECT Month,
       Sales,
       LEAD(Sales) OVER
       (
       ORDER BY Month
       ) NextMonth
FROM Sales;

---

First Value

SELECT *,
FIRST_VALUE(Salary) OVER
(
PARTITION BY DeptID
ORDER BY Salary DESC
)
FROM Employee;

Highest salary in department.

---

Top 3 Employees Per Department

Very common interview question.

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

---

Top SQL Queries for Practice (With Short Answers) - Real-Time Scenario Queries (81–100)

 

81. Find duplicate transactions
    Use GROUP BY transaction_id HAVING COUNT(*) > 1.
82. Find inactive customers
    Find customers with no orders in last 6 months.
83. Find repeat customers
    Group by customer and filter COUNT(order_id) > 1.
84. Find one-time customers
    Group by customer and filter COUNT(order_id) = 1.
85. Find top-selling products
    Group by product and order by total quantity sold desc.
86. Find least-selling products
    Group by product and order by total quantity sold asc.
87. Find average order value
    SUM(order_amount) / COUNT(order_id).
88. Find monthly revenue
    Group by month and sum order amount.
89. Find YoY sales growth
    Compare current year sales with previous year sales.
90. Find MoM sales growth
    Use LAG() on monthly sales.
91. Find customer lifetime value
    Sum total purchase amount by customer.
92. Find customer churn
    Find customers with no recent activity.
93. Find best performing region
    Group by region and sort by sales desc.
94. Find worst performing region
    Group by region and sort by sales asc.
95. Find null-heavy columns
    Use COUNT(*) - COUNT(column).
96. Find bad quality records
    Check NULLs, duplicates, invalid formats.
97. Find orphan records
    Use LEFT JOIN and filter unmatched child records.
98. Validate source vs target count
    Compare COUNT(*) from both tables.
99. Find changed records in ETL
    Compare source and target using hash/timestamp.
100. Find load failures in ETL
     Check audit table, rejected rows, and error logs.

Top SQL Queries for Practice (With Short Answers) - Window Function Queries (61–80)

61. Assign row numbers to employees

    SELECT emp_name, ROW_NUMBER() OVER (ORDER BY salary DESC) rn
    FROM Employee;

62. Rank employees by salary

    SELECT emp_name, RANK() OVER (ORDER BY salary DESC) rnk
    FROM Employee;

63. Dense rank employees by salary

    SELECT emp_name, DENSE_RANK() OVER (ORDER BY salary DESC) drnk
    FROM Employee;

64. Find top 3 salaries

    SELECT *
    FROM (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) drnk
    FROM Employee
    ) x
    WHERE drnk <= 3;

65. Find top 3 salaries in each department

    SELECT *
    FROM (
    SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id 
    ORDER BY salary DESC) drnk
    FROM Employee
    ) x
    WHERE drnk <= 3;

66. Find highest salary in each department

    SELECT *
    FROM (
    SELECT *, RANK() OVER (PARTITION BY dept_id 
    ORDER BY salary DESC) rnk
    FROM Employee
    ) x
    WHERE rnk = 1;

67. Calculate running salary total

    SELECT emp_name, salary,
    SUM(salary) OVER (ORDER BY emp_id) running_total
    FROM Employee;

68. Calculate cumulative sales

    SELECT month, sales,
    SUM(sales) OVER (ORDER BY month) cumulative_sales
    FROM Sales;

69. Get previous salary value

    SELECT emp_name, salary,
    LAG(salary) OVER (ORDER BY emp_id) prev_salary
    FROM Employee;

70. Get next salary value

    SELECT emp_name, salary,
    LEAD(salary) OVER (ORDER BY emp_id) next_salary
    FROM Employee;

71. Compare current and previous month sales

    SELECT month, sales,
    LAG(sales) OVER (ORDER BY month) prev_month_sales
    FROM Sales;

72. Find salary difference from previous employee

    SELECT emp_name, salary,
    salary - LAG(salary) OVER (ORDER BY emp_id) diff
    FROM Employee;

73. Find first salary in each department

    SELECT *,
    FIRST_VALUE(salary) OVER (PARTITION 
    BY dept_id ORDER BY salary DESC)
    FROM Employee;

74. Find last salary in each department

    SELECT *,
    LAST_VALUE(salary) OVER (PARTITION BY 
    dept_id ORDER BY salary DESC)
    FROM Employee;

75. Find average salary by department without grouping

    SELECT emp_name, dept_id,
    AVG(salary) OVER (PARTITION BY dept_id) avg_salary
    FROM Employee;

76. Find percent contribution of salary

    SELECT emp_name, salary,
    salary * 100.0 / SUM(salary) OVER() pct
    FROM Employee;

77. Find duplicate rows using row_number

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION 
    BY emp_name ORDER BY emp_id) rn
    FROM Employee
    ) x
    WHERE rn > 1;

78. Delete duplicate rows
    Use above query in CTE and delete where rn > 1.
79. Find latest order per customer

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 
    customer_id ORDER BY order_date DESC) rn
    FROM Orders
    ) x
    WHERE rn = 1;

80. Find first order per customer

    SELECT *
    FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 
    customer_id ORDER BY order_date ASC) rn
    FROM Orders
    ) x
    WHERE rn = 1;

---

Top SQL Queries for Practice (With Short Answers) - Aggregate Queries (16–30)

 

Aggregate Queries (16–30)

16. Count total employees

SELECT COUNT(*) FROM Employee;

17. Count employees in HR

SELECT COUNT(*) FROM Employee WHERE department = 'HR';

18. Find total salary payout

SELECT SUM(salary) FROM Employee;

19. Find average salary

SELECT AVG(salary) FROM Employee;

20. Find highest salary

SELECT MAX(salary) FROM Employee;

21. Find lowest salary

SELECT MIN(salary) FROM Employee;

22. Count employees by department

SELECT department, COUNT(*) FROM Employee GROUP BY department;

23. Find avg salary by department

SELECT department, AVG(salary) FROM Employee GROUP BY department;

24. Find highest salary by department

SELECT department, MAX(salary) FROM Employee GROUP BY department;

25. Find departments with more than 5 employees

SELECT department, COUNT(*) 
FROM Employee
GROUP BY department
HAVING COUNT(*) > 5;

26. Find departments with avg salary > 60000

SELECT department, AVG(salary)
FROM Employee
GROUP BY department
HAVING AVG(salary) > 60000;

27. Count distinct departments

SELECT COUNT(DISTINCT department) FROM Employee;

28. Find total salary by department

SELECT department, SUM(salary) FROM Employee GROUP BY department;

29. Find total employees in each location

SELECT location, COUNT(*) FROM Employee GROUP BY location;

30. Find max salary where department = IT

SELECT MAX(salary) FROM Employee WHERE department = 'IT';

---

Top SQL Queries for Practice (With Short Answers) - Basic Select Queries (1–15)

 

1. Fetch all records from Employee table

    SELECT * FROM Employee;

2. Fetch only employee names

    SELECT emp_name FROM Employee;

3. Fetch unique department names

    SELECT DISTINCT department FROM Employee;

4. Fetch employees with salary > 50000

    SELECT * FROM Employee WHERE salary > 50000;

5. Fetch employees from HR department

    SELECT * FROM Employee WHERE department = 'HR';

6. Fetch employees with salary between 30000 and 60000

    SELECT * FROM Employee WHERE salary BETWEEN 30000 AND 60000;

7. Fetch employees in HR or IT

    SELECT * FROM Employee WHERE department IN ('HR','IT');

8. Fetch employees whose name starts with A

    SELECT * FROM Employee WHERE emp_name LIKE 'A%';

9. Fetch employees whose name ends with n

    SELECT * FROM Employee WHERE emp_name LIKE '%n';

10. Fetch employees whose name contains 'ar'

    SELECT * FROM Employee WHERE emp_name LIKE '%ar%';

11. Fetch employees with NULL manager_id

    SELECT * FROM Employee WHERE manager_id IS NULL;

12. Fetch employees sorted by salary ascending

    SELECT * FROM Employee ORDER BY salary ASC;

13. Fetch employees sorted by salary descending

    SELECT * FROM Employee ORDER BY salary DESC;

14. Fetch top 5 highest paid employees

    SELECT TOP 5 * FROM Employee ORDER BY salary DESC;

15. Fetch first 5 rows (MySQL)

    SELECT * FROM Employee LIMIT 5;