Find the Start and End Number of Continuous Ranges

Find the Start and End Number of Continuous Ranges - Problem

Database Medium

Find the Start and End Number of Continuous Ranges

You are given a database table Logs containing unique log IDs. Your task is to identify continuous ranges of log IDs and return the start and end points of each range.

Table Schema:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| log_id        | int     |
+---------------+---------+

log_id is the column of unique values for this table.

Problem: Write a SQL solution to find the start and end number of continuous ranges in the table. A continuous range is a sequence of consecutive integers (e.g., 1,2,3,4 or 7,8,9).

Output: Return the result table ordered by start_id. Each row should contain the starting and ending log_id of a continuous range.

Example: If you have log_ids [1,2,3,7,8,15], the continuous ranges are: [1,3], [7,8], and [15,15].

Input & Output

example_1.sql — Basic Case

$ Input: Logs table: +--------+ | log_id | +--------+ | 1 | | 2 | | 3 | | 7 | | 8 | | 15 | +--------+

› Output: +----------+--------+ | start_id | end_id | +----------+--------+ | 1 | 3 | | 7 | 8 | | 15 | 15 | +----------+--------+

💡 Note: The log_ids 1,2,3 form a continuous range [1,3]. The log_ids 7,8 form range [7,8]. The single log_id 15 forms range [15,15].

example_2.sql — Single Elements

$ Input: Logs table: +--------+ | log_id | +--------+ | 1 | | 3 | | 5 | +--------+

› Output: +----------+--------+ | start_id | end_id | +----------+--------+ | 1 | 1 | | 3 | 3 | | 5 | 5 | +----------+--------+

💡 Note: Each log_id is isolated, so each forms its own range where start_id equals end_id.

example_3.sql — Large Continuous Range

$ Input: Logs table: +--------+ | log_id | +--------+ | 10 | | 11 | | 12 | | 13 | | 14 | | 20 | +--------+

› Output: +----------+--------+ | start_id | end_id | +----------+--------+ | 10 | 14 | | 20 | 20 | +----------+--------+

💡 Note: Log_ids 10,11,12,13,14 form one large continuous range [10,14], while 20 forms a single-element range [20,20].

Visualization

Tap to expand

Understanding the Visualization

Sort Books

Arrange all book numbers in ascending order

Number Positions

Assign position numbers (1, 2, 3, ...) to each book

Find Pattern

Calculate book_number - position for each book

Group Sequences

Books with same difference value form continuous sequences

Extract Ranges

Find first and last book in each sequence group

Key Takeaway

🎯 Key Insight: The ROW_NUMBER() window function creates a perfect grouping mechanism - consecutive numbers will always have the same log_id - row_number difference!

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Each row is compared against every other row to find consecutive sequences

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary space for join operations and result storage

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 10⁴
1 ≤ log_id ≤ 10⁷
All log_id values are unique
Result must be ordered by start_id

Asked in

a Amazon 35 G Google 28 ⊞ Microsoft 22 f Meta 15

The optimal approach uses the ROW_NUMBER() window function to create groupings. Key insight: consecutive numbers have the same log_id - ROW_NUMBER() difference. Group by this difference and find MIN/MAX for each group to get range boundaries. Time: O(n log n) for sorting, Space: O(n).

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Self-Join Approach)	O(n²)	O(n)	Use multiple self-joins to check for consecutive sequences
Window Function with ROW_NUMBER	O(n log n)	O(n)	Use ROW_NUMBER() to create groupings for continuous sequences

Brute Force (Self-Join Approach) — Algorithm Steps

Join the table with itself to find consecutive pairs
Use multiple queries to identify range starts and ends
Combine results to form complete ranges
Handle single-element ranges separately

Visualization

Tap to expand

Step-by-Step Walkthrough

Sort Data

First sort all log_ids to process them in order

Check Consecutive

For each ID, check if next ID is consecutive

Form Range

Group consecutive IDs into ranges

Code -

solution.c — C

// SQL query implementation in C
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int start;
    int end;
} Range;

int compare(const void *a, const void *b) {
    return (*(int*)a - *(int*)b);
}

int findContinuousRangesBruteForce(int* logIds, int size, Range* result) {
    qsort(logIds, size, sizeof(int), compare);
    int resultCount = 0;
    
    int i = 0;
    while (i < size) {
        int start = logIds[i];
        int end = start;
        
        // Find end of continuous range
        while (i + 1 < size && logIds[i + 1] == logIds[i] + 1) {
            i++;
            end = logIds[i];
        }
        
        result[resultCount].start = start;
        result[resultCount].end = end;
        resultCount++;
        i++;
    }
    
    return resultCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Each row is compared against every other row to find consecutive sequences

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary space for join operations and result storage

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 10⁴
1 ≤ log_id ≤ 10⁷
All log_id values are unique
Result must be ordered by start_id

78.2K Views

Medium-High Frequency

~18 min Avg. Time

1.8K 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

Find the Start and End Number of Continuous Ranges

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Self-Join Approach) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler