-
Notifications
You must be signed in to change notification settings - Fork 1
/
180_Consecutive_Numbers.sql
70 lines (58 loc) · 1.77 KB
/
180_Consecutive_Numbers.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- Source: https://leetcode.com/problems/consecutive-numbers/description/?envType=study-plan-v2&envId=top-sql-50
-- Table: Logs
-- +-------------+---------+
-- | Column Name | Type |
-- +-------------+---------+
-- | id | int |
-- | num | varchar |
-- +-------------+---------+
-- In SQL, id is the primary key for this table.
-- id is an autoincrement column.
-- Find all numbers that appear at least three times consecutively.
-- Return the result table in any order.
-- The result format is in the following example.
-- Example 1:
-- Input:
-- Logs table:
-- +----+-----+
-- | id | num |
-- +----+-----+
-- | 1 | 1 |
-- | 2 | 1 |
-- | 3 | 1 |
-- | 4 | 2 |
-- | 5 | 1 |
-- | 6 | 2 |
-- | 7 | 2 |
-- +----+-----+
-- Output:
-- +-----------------+
-- | ConsecutiveNums |
-- +-----------------+
-- | 1 |
-- +-----------------+
-- Explanation: 1 is the only number that appears consecutively for at least three times.
------------------------------------------------------------------------------
-- SQL Schema
Create table If Not Exists Logs (id int, num int)
Truncate table Logs
insert into Logs (id, num) values ('1', '1')
insert into Logs (id, num) values ('2', '1')
insert into Logs (id, num) values ('3', '1')
insert into Logs (id, num) values ('4', '2')
insert into Logs (id, num) values ('5', '1')
insert into Logs (id, num) values ('6', '2')
insert into Logs (id, num) values ('7', '2')
-- MS SQL Server Code
SELECT DISTINCT l.num AS 'ConsecutiveNums'
FROM (
SELECT id,
LAG(id, 2) OVER(ORDER BY id) AS id3,
num,
LAG(num, 1) OVER(ORDER BY id) AS num2,
LAG(num, 2) OVER(ORDER BY id) AS num3
FROM Logs
) AS l
WHERE l.num = l.num2
AND l.num = l.num3
AND id - 2 = id3;