Description

Table: Logs

Column NameType
log_idint
  • log_id is the column of unique values for this table.
  • Each row of this table contains the ID in a log Table.

Problem Statement

Write a solution to find the start and end number of continuous ranges in the table Logs.

Return the result table ordered by start_id. The result format is in the following example.

Example 1:

Input:

  • Logs table:
log_id
1
2
3
7
8
10

Output:

start_idend_id
13
78
1010

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

Solution

This is a problem to roll few of the logs into single entries. If you perform window operation, you will see some patterns emerge out of the results.

1WITH numbered_logs AS (
2    SELECT log_id,
3        ROW_NUMBER() OVER (ORDER BY log_id) AS rn
4        FROM Logs
5) SELECT * FROM numbered_logs;
log_idrn
11
22
33
74
85
106

Although this by itself doesn’t show anything important, if you look carefully, you will see that the difference between log_id and rn remains same for the logs which are in some range. Let’s see the difference as a table.

1WITH numbered_logs AS (
2    SELECT log_id,
3        ROW_NUMBER() OVER (ORDER BY log_id) AS rn
4        FROM Logs
5) SELECT log_id, rn, log_id - rn FROM numbered_logs;
log_idrnlog_id - rn
110
220
330
743
853
1064

Now, with above result, you can easily find the start and end of the ranges by grouping them using log_id - rn column and finding the min and max of log_id. This is the final answer.

1WITH numbered_logs AS (
2    SELECT log_id,
3        ROW_NUMBER() OVER (ORDER BY log_id) AS rn
4        FROM Logs
5) SELECT MIN(log_id) AS start_id, MAX(log_id) AS end_id
6    FROM numbered_logs
7    GROUP BY (log_id - rn)
8    ORDER BY start_id;