0% completed
Problem Statement
Table: Sequence
Each row in this table represents a sequence of numbers with a unique ID and a value for each position in the sequence.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| value | int |
+-------------+---------+
id is the primary key for this table.
This table may contain duplicate values in the 'value' column.
Develop a solution to find all values that appear at least three times consecutively in the sequence.
Return the result table in any order.
Example
Input:
Sequence table: +----+-------+ | id | value | +----+-------+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 200 | | 5 | 100 | | 6 | 200 | | 7 | 200 | +----+-------+
Output:
+-----------------+ | RepeatedValues | +-----------------+ | 100 | +-----------------+
In this example, 100 is the only value that appears consecutively for at least three times.
Try It Yourself
Solution
To identify all values that appear at least three times consecutively in the Sequence
table, we can utilize SQL window functions such as LAG
and LEAD
. This approach allows us to examine adjacent rows efficiently and determine if a value repeats consecutively across the desired number of positions.
Approach Overview
- Use Window Functions to Access Adjacent Rows: Employ
LAG
to retrieve the previous value andLEAD
to retrieve the next value for each row. - Identify Values with Three Consecutive Repetitions: Select values where the current value matches both the previous and next values, indicating three consecutive occurrences.
- Eliminate Duplicate Results: Use
DISTINCT
to ensure each repeated value appears only once in the final output.
SQL Query
WITH Consecutive AS ( SELECT value, LAG(value, 1) OVER (ORDER BY id) AS prev_value, LEAD(value, 1) OVER (ORDER BY id) AS next_value FROM Sequence ) SELECT DISTINCT value AS RepeatedValues FROM Consecutive WHERE value = prev_value AND value = next_value;
Step-by-Step Approach
Step 1: Use Window Functions to Access Adjacent Rows
Retrieve each value along with its immediate predecessor and successor to identify potential consecutive repetitions.
SQL Query:
WITH Consecutive AS ( SELECT value, LAG(value, 1) OVER (ORDER BY id) AS prev_value, LEAD(value, 1) OVER (ORDER BY id) AS next_value FROM Sequence )
Explanation:
WITH Consecutive AS (...)
:- Defines a Common Table Expression (CTE) named
Consecutive
that prepares the data for further analysis.
- Defines a Common Table Expression (CTE) named
SELECT value, LAG(value, 1) OVER (ORDER BY id) AS prev_value, LEAD(value, 1) OVER (ORDER BY id) AS next_value
:value
: The current value in the sequence.LAG(value, 1) OVER (ORDER BY id) AS prev_value
: Retrieves the value from the previous row based on theid
ordering.LEAD(value, 1) OVER (ORDER BY id) AS next_value
: Retrieves the value from the next row based on theid
ordering.
FROM Sequence
:- Indicates that the data is sourced from the
Sequence
table.
- Indicates that the data is sourced from the
Output After Step 1:
+-------+------------+------------+ | value | prev_value | next_value | +-------+------------+------------+ | 100 | NULL | 100 | | 100 | 100 | 100 | | 100 | 100 | 200 | | 200 | 100 | 100 | | 100 | 200 | 200 | | 200 | 100 | 200 | | 200 | 200 | NULL | +-------+------------+------------+
Step 2: Identify Values with Three Consecutive Repetitions
Filter the records to find values that are the same as both their preceding and succeeding values, indicating three consecutive occurrences.
SQL Query:
SELECT DISTINCT value AS RepeatedValues FROM Consecutive WHERE value = prev_value AND value = next_value;
Explanation:
SELECT DISTINCT value AS RepeatedValues
:- Selects unique values that meet the repetition criteria and aliases the column as
RepeatedValues
.
- Selects unique values that meet the repetition criteria and aliases the column as
FROM Consecutive
:- Utilizes the
Consecutive
CTE defined in Step 1.
- Utilizes the
WHERE value = prev_value AND value = next_value
:- Filters rows where the current
value
is equal to bothprev_value
andnext_value
, ensuring that the value appears consecutively at least three times.
- Filters rows where the current
Output After Step 2:
+-----------------+ | RepeatedValues | +-----------------+ | 100 | +-----------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible