Tip of the Day : LeetCode 180 - Consecutive Numbers

# Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

# SQL Server Tip of the Day - September 15, 2024

## LeetCode 180 - Consecutive Numbers

Database Language: SQL Server

Difficulty: Medium

## Problem Description

### Input

#### Table: Logs

```| Column Name | Type    |
| ----------- | ------- |
| id          | int     |
| num         | varchar |
```

In SQL, `id` is the primary key for this table. `id` is an autoincrement column.

### Requirement

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.

### Examples

#### 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 [dbo].[Logs] (id INT PRIMARY KEY, num INT);

TRUNCATE TABLE [dbo].[Logs];
INSERT INTO [dbo].[Logs] (id, num) VALUES ('1', '1');
INSERT INTO [dbo].[Logs] (id, num) VALUES ('2', '1');
INSERT INTO [dbo].[Logs] (id, num) VALUES ('3', '1');
INSERT INTO [dbo].[Logs] (id, num) VALUES ('4', '2');
INSERT INTO [dbo].[Logs] (id, num) VALUES ('5', '1');
INSERT INTO [dbo].[Logs] (id, num) VALUES ('6', '2');
INSERT INTO [dbo].[Logs] (id, num) VALUES ('7', '2');
```

## Solutions

There are a couple of ways of finding all numbers that appear at least three times consecutively. First is by using INNER JOIN and joining the table to itself 3 times. Second is by using the EXISTS operator in the WHERE clause.

### Solution #1 - Using INNER JOIN

To find all numbers that appear at least three times consecutively using INNER JOIN, the Logs table need to be self-joined to itself three times because we are looking for cases when a number appear consecutively at least three times.

Before finding all numbers that appear at least three times consecutively, let's first try to find all numbers that appear at least 2 times consecutively. The following query can be used to accomplish this:

```SELECT DISTINCT L1.num AS ConsecutiveNums
FROM Logs L1 INNER JOIN Logs L2
ON L1.id + 1 = L2.id AND L1.num = L2.num
```

Based on this query, we are looking for a row where the current ID and the succeeding ID (`L1.id + 1 = L2.id`) have a `num` value that are the same (`L1.num = L2.num`).

The DISTINCT clause needs to be added in the query because a number can appear 2 times consecutively multiple times.

But the requirement is to find all numbers that appear at least three times consecutively instead of two times. The query can be extended by joining the `Logs` table again through the addition of the following INNER JOIN:

```             INNER JOIN Logs L3
ON L2.id + 1 = L3.id AND L2.num = L3.num
```

The full query now looks as follows:

```# Final Solution 1 Query - Using INNER JOIN
SELECT DISTINCT L1.num AS ConsecutiveNums
FROM Logs L1 INNER JOIN Logs L2
ON L1.id + 1 = L2.id AND L1.num = L2.num
INNER JOIN Logs L3
ON L2.id + 1 = L3.id AND L2.num = L3.num
```

The query plan for this query is as follows:

```  |--Sort(DISTINCT ORDER BY:([L1].[num] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([L2].[num], [Expr1003]))
|--Compute Scalar(DEFINE:([Expr1003]=[leetcode].[dbo].[Logs].[id] as [L2].[id]+(1)))
|   |--Nested Loops(Inner Join, OUTER REFERENCES:([L1].[num], [Expr1004]))
|       |--Compute Scalar(DEFINE:([Expr1004]=[leetcode].[dbo].[Logs].[id] as [L1].[id]+(1)))
|       |   |--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L1]))
|       |--Clustered Index Seek(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L2]),
SEEK:([L2].[id]=[Expr1004]),
WHERE:([leetcode].[dbo].[Logs].[num] as [L1].[num]=[leetcode].[dbo].[Logs].[num] as [L2].[num])
ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L3]),
SEEK:([L3].[id]=[Expr1003]),
WHERE:([leetcode].[dbo].[Logs].[num] as [L2].[num]=[leetcode].[dbo].[Logs].[num] as [L3].[num])
ORDERED FORWARD)
```

The fastest runtime for the query above is as follows:

• Runtime: 533ms

• Beats: 81.53% as of July 16, 2024

This query joins the `L1` Logs table with `L2` Logs table and then the `L2` Logs table with `L3` Logs table. Another way of joining the Logs tables is between the `L1` Logs table with `L2` Logs table and then between the `L1` Logs table with `L3` Logs table. The query will look as follows:

```# Final Solution 1 Query - Using INNER JOIN
SELECT DISTINCT L1.num AS ConsecutiveNums
FROM Logs L1 INNER JOIN Logs L2
ON L1.id + 1 = L2.id AND L1.num = L2.num
INNER JOIN Logs L3
ON L1.id + 2 = L3.id AND L1.num = L3.num
```

The query plan for this alternative query is as follows:

```  |--Sort(DISTINCT ORDER BY:([L1].[num] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([L3].[num], [Expr1004]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([L1].[num], [Expr1003]))
|   |--Compute Scalar(DEFINE:([Expr1003]=[leetcode].[dbo].[Logs].[id] as [L1].[id]+(2),
[Expr1004]=[leetcode].[dbo].[Logs].[id] as [L1].[id]+(1)))
|--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L1]))
|--Clustered Index Seek(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L3]),
SEEK:([L3].[id]=[Expr1003]),
WHERE:([leetcode].[dbo].[Logs].[num] as [L1].[num]=[leetcode].[dbo].[Logs].[num] as [L3].[num])
ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L2]),
SEEK:([L2].[id]=[Expr1004]),
WHERE:([leetcode].[dbo].[Logs].[num] as [L3].[num]=[leetcode].[dbo].[Logs].[num] as [L2].[num])
ORDERED FORWARD)
```

The fastest runtime for the query above is as follows:

• Runtime: 525ms

• Beats: 83.15% as of July 16, 2024

### Solution #2 - Using EXISTS

The second way of finding all numbers that appear at least three times consecutively is with the use of the EXISTS clause twice as part of the WHERE condition. The first EXISTS clause will check if the `num` value of the current ID is the same as the `num` value of the succeeding ID:

```EXISTS (SELECT 'X' FROM Logs L2 WHERE L1.id + 1 = L2.id AND L1.num = L2.num)
```

The second EXISTS condition will check if the `num` value of the current ID is the same as the `num` value of the ID after the next ID (`L1.id + 2 = L3.id`):

```EXISTS (SELECT 'X' FROM Logs L3 WHERE L1.id + 2 = L3.id AND L1.num = L3.num)
```

The full query that uses the EXISTS clause twice is as follows:

```# Final Solution 2 Query - Using EXISTS
SELECT DISTINCT num AS ConsecutiveNums
FROM Logs L1
WHERE EXISTS (SELECT 'X' FROM Logs L2 WHERE L1.id + 1 = L2.id AND L1.num = L2.num) AND
EXISTS (SELECT 'X' FROM Logs L3 WHERE L1.id + 2 = L3.id AND L1.num = L3.num)
```

The query plan generated by SQL Server for this query is as follows:

```  |--Sort(DISTINCT ORDER BY:([L1].[num] ASC))
|--Nested Loops(Left Semi Join, WHERE:([Expr1005]=[leetcode].[dbo].[Logs].[id] as [L3].[id] AND
[leetcode].[dbo].[Logs].[num] as [L1].[num]=[leetcode].[dbo].[Logs].[num] as [L3].[num]))
|--Nested Loops(Left Semi Join, WHERE:([Expr1006]=[leetcode].[dbo].[Logs].[id] as [L2].[id] AND
[leetcode].[dbo].[Logs].[num] as [L1].[num]=[leetcode].[dbo].[Logs].[num] as [L2].[num]))
|   |--Compute Scalar(DEFINE:([Expr1005]=[leetcode].[dbo].[Logs].[id] as [L1].[id]+(2),
[Expr1006]=[leetcode].[dbo].[Logs].[id] as [L1].[id]+(1)))
|   |   |--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L1]))
|   |--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L2]))
|--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Logs].[PK_Logs] AS [L3]))
```

And the fastest runtime for this query is as follows:

• Runtime: 576ms

• Beats: 69.30% as of July 16, 2024

### Solution Runtime Comparison

Here's the comparison of the fastest runtime for each of the solutions.

```| Solution #            | Runtime | Beats  |
| --------------------- | ------- | ------ |
| 1a - Using INNER JOIN |  533ms  | 81.53% |
| 1b - Using INNER JOIN |  525ms  | 83.15% |
| 2 - Using EXISTS      |  576ms  | 69.30% |
```

# SQL Server 2008

### What's New in SQL Server 2008

A collection of articles related to what's new with SQL Server 2008:

### SQL Server 2008 Articles

A collection of articles related to SQL Server 2008:

# User-Defined Functions

### Date Functions

A collection of useful user-defined functions that deal with dates.

### String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

### Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

### Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

### SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

# Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

### SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them: