Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1285.html
1285. Find the Start and End Number of Continuous Ranges
Level
Medium
Description
Table: Logs
+---------------+---------+
| 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 query result format is in the following example:
Logs table:
+------------+
| log_id |
+------------+
| 1 |
| 2 |
| 3 |
| 7 |
| 8 |
| 10 |
+------------+
Result table:
+------------+--------------+
| start_id | end_id |
+------------+--------------+
| 1 | 3 |
| 7 | 8 |
| 10 | 10 |
+------------+--------------+
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.
Solution
For each value log in table Logs, if log - 1 does not exist in Logs, then log is the start id of an interval. If log + 1 does not exist in Logs, then log is the end id of an interval. Join the start ids and the end ids to obtain the result.
# Write your MySQL query statement below
select log_start.log_id as START_ID, min(log_end.log_id) as END_ID from
(select log_id from logs where log_id - 1 not in (select * from Logs)) log_start,
(select log_id from logs where log_id + 1 not in (select * from Logs)) log_end
where log_start.log_id <= log_end.log_id
group by log_start.log_id;