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_id and end_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_id values are unique
  • The table contains at least 1 row

Visualization

Tap to expand
Find Start and End of Continuous Ranges INPUT Logs Table log_id 1 2 3 7 8 10 Visual: Continuous Ranges 1,2,3 7,8 10 3 separate ranges found ALGORITHM STEPS 1 Create Row Numbers ROW_NUMBER() OVER (ORDER BY log_id) 2 Calculate Difference diff = log_id - row_num Same diff = same group 3 Group by Difference GROUP BY diff value to identify ranges 4 Get MIN/MAX MIN(log_id) as start_id MAX(log_id) as end_id Example Calculation: id row diff 1 1 0 2 2 0 3 3 0 7 4 3 8 5 3 10 6 4 Same diff = continuous range FINAL RESULT Output Table start_id end_id 1 3 7 8 10 10 SQL Query: SELECT MIN(log_id) start_id, MAX(log_id) end_id FROM (SELECT log_id, log_id - ROW_NUMBER() OVER(ORDER BY log_id) AS diff FROM Logs) OK - 3 ranges found Key Insight: The difference between log_id and its row number stays CONSTANT within a continuous range. When there's a gap in IDs, the difference changes. This creates a unique group identifier for each continuous range, allowing us to use GROUP BY to find MIN/MAX of each range. TutorialsPoint - Find the Start and End Number of Continuous Ranges | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Facebook 8
28.5K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen