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
- Missing IDs
- Missing Dates
- Consecutive Days
- Longest Streak
- Customer Churn
- Gap Detection
- Running Sequences
- Monthly Activity Streaks
- Consecutive Sales
- Islands of Continuous Records
Gap & Island Problems (1–20)
ReplyDelete