Gap & Island Problems (1–20)

 

These questions are extremely popular because they test:

• Window Functions
• LAG/LEAD
• Date Logic
• Real-world Analytics

---

1. Find Missing Employee IDs

Data

ID
1
2
3
5
6

Find:

4

Solution

SELECT ID+1 MissingID
FROM Employee
WHERE ID+1 NOT IN
(
SELECT ID
FROM Employee
);

---

2. Find Missing Invoice Numbers

Data

1001
1002
1003
1005
1006

Missing:

1004

Solution

SELECT InvoiceNo+1
FROM Invoice
WHERE InvoiceNo+1 NOT IN
(
SELECT InvoiceNo
FROM Invoice
);

---

3. Find Gaps Between IDs

Data

1
2
3
7
8
15

Solution

SELECT ID,
       LEAD(ID)
       OVER(ORDER BY ID) NextID
FROM Employee;

Find rows where:

NextID - ID > 1

---

4. Consecutive Login Days

Data

User	Date
A	Jan1
A	Jan2
A	Jan3

Find consecutive streaks.

Solution

ROW_NUMBER()
DATEDIFF()

Gap-and-island technique.

---

5. Longest Login Streak

Example

User A
5 consecutive days

Uses

ROW_NUMBER()
PARTITION BY User

---

6. Consecutive Sales Days

Find stores having sales every day for 7 days.

Uses:

LAG()
DATEDIFF()

---

7. Consecutive Order Dates

Data

1-Jan
2-Jan
3-Jan
10-Jan
11-Jan

Find islands:

1-Jan to 3-Jan

10-Jan to 11-Jan

---

8. Customers Ordering Every Month

Find customers ordering in:

Jan
Feb
Mar

without break.

---

9. Missing Months in Sales Data

Data

Jan
Feb
Apr
May

Missing:

Mar

---

10. Detect Missing Dates

Useful for ETL validation.

Data

2025-01-01
2025-01-02
2025-01-04

Missing:

2025-01-03

---

11. Detect Missing Transactions

Transaction IDs:

1001
1002
1005
1006

Missing:

1003
1004

---

12. Find Consecutive Salary Increases

Salary History

Date	Salary
Jan	50K
Feb	55K
Mar	60K

Find increasing streak.

---

13. Find Consecutive Declining Sales

Sales

100
95
90
85

Use:

LAG(Sales)

---

14. Detect Customer Churn

Find customers who stopped ordering for:

90 days

---

15. Find Repeated Status Changes

Example

Open
Open
Open
Closed
Closed

Use:

LAG(Status)

---

16. Longest Consecutive Winning Streak

Sports example.

Data

W
W
W
L
W

Longest streak:

3

---

17. Consecutive Attendance Days

Employees attending continuously.

Uses:

ROW_NUMBER()

---

18. Detect Break in Attendance

Jan1
Jan2
Jan5

Gap:

Jan3-Jan4

---

19. Consecutive Patient Visits

Life Sciences Interview Favorite

Example

Patient visited:

Jan
Feb
Mar

Continuous engagement.

---

20. Classic Interview Question

Find Islands

Input

1
2
3
7
8
9
15
16

Output

1-3

7-9

15-16

Solution Pattern

ID -
ROW_NUMBER() OVER(ORDER BY ID)

Group by derived value.

---

Why Interviewers Love Gap & Island Questions

They test:

✅ Window Functions
✅ Analytical Thinking
✅ Date Logic
✅ Real-world BI Reporting
✅ SQL Problem Solving

Most Asked

1. Missing IDs
2. Missing Dates
3. Consecutive Days
4. Longest Streak
5. Customer Churn
6. Gap Detection
7. Running Sequences
8. Monthly Activity Streaks
9. Consecutive Sales
10. Islands of Continuous Records

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());