Skip to content

Prepare For Coder Interview – Denny

  • Basic
  • Medium
  • Hard
  • Architect
  • Life

LeetCode: Find the Start and End Number of Continuous Ranges

Posted on August 5, 2019July 26, 2020 by braindenny

Find the Start and End Number of Continuous Ranges



Similar Problems:

  • CheatSheet: SQL & MySql
  • CheatSheet: Leetcode For Code Interview
  • CheatSheet: Common Code Problems & Follow-ups
  • Tag: #sql

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.

Github: code.dennyzhang.com

Credits To: leetcode.com

Leave me comments, if you have better ways to solve.


  • Solution: Add temporary columns of rank and prev
## https://code.dennyzhang.com/find-the-start-and-end-number-of-continuous-ranges
select min(log_id) as START_ID, max(log_id) as END_ID
from (select log_id,
        @rank := case when @prev = log_id-1 then @rank else @rank+1 end as rank,
        @prev := log_id as prev
    from Logs,
       (select @rank:=0, @prev:=-1) as rows) as tt
group by rank
order by START_ID
  • Solution: Find the starting and ending sequences, then merge two as one table
## https://code.dennyzhang.com/find-the-start-and-end-number-of-continuous-ranges
## find the starting sequence: 1, 7, 10
## find the ending sequence: 3, 8, 10
## merge them as one table
select start_id, min(end_id) as end_id
from (select t1.log_id as start_id
    from logs as t1 left join logs as t2
    on t1.log_id-1 = t2.log_id
    where t2.log_id is null) tt_start join
    (select t1.log_id as end_id
    from logs as t1 left join logs as t2
    on t1.log_id+1 = t2.log_id
    where t2.log_id is null) tt_end
where start_id<=end_id
group by start_id
linkedin
github
slack

Post Views: 0
Posted in MediumTagged sql

Post navigation

LeetCode: Report Contiguous Dates
LeetCode: Palindrome Removal

Leave a Reply Cancel reply

Your email address will not be published.

Tags

#array #backtracking #bfs #binarytree #bitmanipulation #blog #classic #codetemplate #combination #dfs #dynamicprogramming #game #graph #greedy #heap #inspiring #interval #linkedlist #manydetails #math #palindrome #recursive #slidingwindow #stack #string #subarray #trie #twopointer #twosum binarysearch editdistance hashmap intervaldp knapsack monotone oodesign presum rectangle redo review rotatelist series sql treetraversal unionfind

Recent Posts

  • a
  • a
  • a
  • a
  • a

Recent Comments

    Archives

    Categories

    • Amusing
    • Basic
    • Easy
    • Hard
    • Life
    • Medium
    • Resource
    • Review
    • Series
    • Uncategorized
    Proudly powered by WordPress | Theme: petals by Aurorum.