LC1285-Find-the-Start-and-End-Number-of-Continuous-Ranges

/*
+---------------+---------+
| 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.
*/


# Write your MySQL query statement below
WITH logs_consec AS(
    SELECT l1.log_id AS prev_log, l2.log_id AS next_log
    FROM    Logs AS l1
            LEFT OUTER JOIN Logs AS l2
            ON l1.log_id + 1 = l2.log_id
    UNION
    SELECT l1.log_id AS prev_log, l2.log_id AS next_log
    FROM    Logs AS l1
            RIGHT OUTER JOIN Logs AS l2
            ON l1.log_id + 1 = l2.log_id
    )
SELECT first_logs.start_id AS start_id,
        MIN(last_logs.end_id) AS end_id
FROM    (   # select log_id from logs_consec that don't have a previous one
            # those are first logs in consecutive logs
            SELECT next_log AS start_id
            FROM logs_consec
            WHERE ISNULL(prev_log)
        ) AS first_logs,
        (
            # select log_id from logs_consec that don't have a next one
            # those are last logs in consecutive logs
            SELECT prev_log AS end_id
            FROM logs_consec
            WHERE ISNULL(next_log)
        ) AS last_logs
WHERE first_logs.start_id <= last_logs.end_id
GROUP BY first_logs.start_id