Last updated on January 22nd, 2025 at 11:15 pm

Here, we see the Find the Start and End Number of Continuous Ranges LeetCode Solution. This Leetcode problem is solved using MySQL and Pandas.

List of all LeetCode Solution

Level of Question

Medium

Find the Start and End Number of Continuous Ranges LeetCode Solution

1. Problem Statement

Column Name	Type
log_id	int

id is the primary key for this table.
Each row of this table contains the ID in a log Table.

Since some IDs have been removed from Logs. Write an SQL query to find the start and end number of continuous ranges in table Logs.
Order the result table by start_id.

The result format is in the following example.

Example 1:
Input:

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 in the table.
Number 10 is contained in the table.

2. Code Implementation in Different Languages

2.1 Find the Start and End Number of Continuous Ranges MySQL

select 
  l1.log_id as start_id, 
  l2.log_id as end_id 
from 
  (
    select 
      log_id 
    from 
      Logs 
    where 
      log_id - 1 not in (
        select 
          * 
        from 
          Logs
      )
  ) as l1, 
  (
    select 
      log_id 
    from 
      Logs 
    where 
      log_id + 1 not in (
        select 
          * 
        from 
          Logs
      )
  ) as l2 
where 
  l1.log_id <= l2.log_id 
group by 
  l1.log_id;

2.2 Find the Start and End Number of Continuous Ranges MySQL (Another approach)

select 
  min(log_id) as start_id, 
  max(log_id) as end_id 
from 
  (
    select 
      *, 
      (@id := @id + 1) as id 
    from 
      logs, 
      (
        select 
          @id := 0
      ) as init
  ) tmp 
group by 
  log_id - id