Find the Start and End Number of Continuous Ranges - Problem
Given a table Logs containing unique log IDs, write a SQL query to find the start and end number of continuous ranges in the log IDs.
A continuous range is a sequence of consecutive integers. For example, if we have log IDs 1, 2, 3, 7, 8, then we have two continuous ranges: [1, 3] and [7, 8].
Requirements:
- Return the result table with columns
start_idandend_id - Order the results by
start_id - Each row represents one continuous range
Table Schema
Logs
| Column Name | Type | Description |
|---|---|---|
log_id
PK
|
int | Unique log identifier |
Primary Key: log_id
Note: Each row contains a unique log ID. The goal is to group consecutive log IDs into ranges.
Input & Output
Example 1 — Multiple Continuous Ranges
Input Table:
| log_id |
|---|
| 1 |
| 2 |
| 3 |
| 7 |
| 8 |
Output:
| start_id | end_id |
|---|---|
| 1 | 3 |
| 7 | 8 |
💡 Note:
The log IDs 1, 2, 3 form one continuous range from 1 to 3. The log IDs 7, 8 form another continuous range from 7 to 8. There's a gap between 3 and 7, so they form separate ranges.
Example 2 — Single Numbers as Ranges
Input Table:
| log_id |
|---|
| 1 |
| 3 |
| 6 |
| 7 |
Output:
| start_id | end_id |
|---|---|
| 1 | 1 |
| 3 | 3 |
| 6 | 7 |
💡 Note:
Individual log IDs 1 and 3 each form their own range where start_id equals end_id. Log IDs 6, 7 are consecutive and form a range from 6 to 7.
Example 3 — All Consecutive Numbers
Input Table:
| log_id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Output:
| start_id | end_id |
|---|---|
| 1 | 5 |
💡 Note:
All log IDs from 1 to 5 are consecutive, forming a single continuous range from 1 to 5.
Constraints
-
1 ≤ log_id ≤ 10^8 -
All
log_idvalues are unique - The table contains at least 1 row
Visualization
Tap to expand
Understanding the Visualization
1
Input
Scattered log IDs with gaps
2
Window Function
ROW_NUMBER creates grouping pattern
3
Output
Continuous ranges with start/end
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER() difference pattern to identify and group continuous sequences efficiently
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code