Skip to main content
Talentd

Find the Start and End Number of Continuous Ranges - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Microsoft
Practice this problem

Problem Statement

Table: Logs

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| log_id        | int     |
+---------------+---------+
log_id is the column of unique values for this table.
Each row of this table contains the ID in a log Table.

 

Write a solution to find the start and end number of continuous ranges in the table Logs.

Return the result table ordered by start_id.

The result format is in the following example.

 

Example 1:

Input: 
Logs table:
+------------+
| log_id     |
+------------+
| 1          |
| 2          |
| 3          |
| 7          |
| 8          |
| 10         |
+------------+
Output: 
+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+
Explanation: 
The result table should contain all ranges in table Logs.
From 1 to 3 is contained in the table.
From 4 to 6 is missing in the table
From 7 to 8 is contained in the table.
Number 9 is missing from the table.
Number 10 is contained in the table.

Approach Overview

Problem Overview: Given a table of numeric log_id values, the goal is to compress consecutive numbers into ranges. Each continuous sequence should return a single row containing the starting number and ending number of that range.

Approach 1: Group By + Window Function (O(n) time, O(n) space)

This approach relies on the observation that consecutive numbers share a constant difference between the value and its row index. Use the ROW_NUMBER() window function to assign a sequential index ordered by log_id. For each row, compute log_id - ROW_NUMBER(). Consecutive numbers produce the same difference, which naturally groups them into ranges. After generating this grouping key, aggregate using GROUP BY and compute MIN(log_id) and MAX(log_id) for each group. The database performs a single ordered scan, giving O(n) time after sorting and O(n) auxiliary space for the window calculation. This pattern appears frequently when solving sequence grouping problems with SQL and window functions.

Approach 2: Default Approach (Session Variables) (O(n) time, O(1) extra space)

MySQL session variables can track when a range starts and ends while scanning rows in sorted order. Sort the table by log_id and maintain variables storing the previous value and the current range start. When the current log_id is not equal to previous + 1, a new range begins. The previous value marks the end of the last range, and the current value becomes the new start. This approach avoids window functions and works well in older MySQL environments that lack advanced analytic features. The query still performs a linear pass through the ordered data, so time complexity is O(n) with constant extra memory.

Recommended for interviews: The window-function grouping approach is the cleanest and most expressive solution. Interviewers expect you to recognize the value - row_number trick for consecutive sequences and combine it with GROUP BY aggregation. The variable-based approach shows deeper knowledge of MySQL internals but is less portable across SQL engines.

Approach 1: Group By + Window Function

We need to find a way to group a continuous sequence of logs into the same group, and then aggregate each group to obtain the start and end logs of each group.

There are two ways to implement grouping:

  1. By calculating the difference between each log and the previous log, if the difference is 1, then the two logs are continuous, and we set delta to 0, otherwise we set it to 1. Then we take the prefix sum of delta to obtain the grouping identifier for each row.
  2. By calculating the difference between the current log and its row number, we obtain the grouping identifier for each row.

Code

MySQL

MySQL

Try this approach in the editor →

Approach 2: Default Approach

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Group By + Window Function—
Default Approach—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Group By + Window FunctionO(n)O(n)Preferred modern SQL solution when window functions like ROW_NUMBER() are available
Session Variables (Default MySQL)O(n)O(1)Useful in MySQL environments without window functions or when minimizing memory usage

Video Solution

LeetCode Medium 1285 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 6,922 views views

Watch 5 more video solutions →

Frequently Asked Questions

Is Find the Start and End Number of Continuous Ranges easy or hard?
The problem is usually classified as Medium because the key insight is not obvious at first. Recognizing that consecutive numbers share a constant difference with their row index enables a concise O(n) SQL solution using window functions and GROUP BY.
Find the Start and End Number of Continuous Ranges Python/Java solution
This problem is typically solved directly in SQL because the input is stored in a database table. In application languages like Python or Java, the equivalent approach would sort the numbers and scan once while tracking the start of each range and detecting breaks when the difference between adjacent values exceeds one.
How to solve Find the Start and End Number of Continuous Ranges in O(n)?
Sort the table by log_id and assign a sequential index using ROW_NUMBER(). For consecutive numbers, the difference log_id - ROW_NUMBER() stays constant. Group rows using this value and calculate MIN(log_id) and MAX(log_id) for each group to obtain the start and end of every continuous range. The database performs a single pass over the rows, giving O(n) complexity.
What is the best approach for Find the Start and End Number of Continuous Ranges?
The most reliable approach uses a SQL window function with ROW_NUMBER(). Compute log_id - ROW_NUMBER() to create a stable grouping key for consecutive numbers, then aggregate each group with MIN(log_id) and MAX(log_id). This runs in O(n) time after ordering and produces a clean, readable query supported by modern SQL databases.
Is Find the Start and End Number of Continuous Ranges asked at Google/Amazon/Meta?
Range compression and consecutive sequence grouping problems frequently appear in SQL interviews at large tech companies including Google, Amazon, and Meta. Variants often require detecting consecutive dates, transaction IDs, or event sequences using window functions or grouping tricks similar to this problem.
What data structure is used in Find the Start and End Number of Continuous Ranges?
The solution primarily relies on SQL window functions and grouping rather than traditional data structures. ROW_NUMBER() creates positional indexing over ordered rows, and GROUP BY aggregates rows that belong to the same consecutive sequence.
What is the time complexity of Find the Start and End Number of Continuous Ranges?
The typical SQL solution runs in O(n) time for scanning the rows once they are ordered by log_id. Window functions such as ROW_NUMBER() process each row exactly once. Space complexity is O(n) for intermediate window results, though variable-based MySQL approaches can reduce extra space to O(1).

Ready to solve this problem?

Practice Find the Start and End Number of Continuous Ranges with our built-in code editor and test cases.

Practice on FleetCode
Previous

Customers With Strictly Increasing Purchases

Next

Find the Missing IDs