Consecutive Numbers - Practice Coding Problems

Consecutive Numbers - Problem

Database Medium

Database Query Challenge: Finding Consecutive Patterns

You are given a database table called Logs that contains numerical data with sequential IDs. Your task is to identify all numbers that appear at least three times consecutively in the table.

Table Schema:
Logs table:
• id (int): Primary key, auto-increment starting from 1
• num (varchar): The number value to analyze

Goal: Write a SQL query to find all unique numbers that appear in three or more consecutive rows. The order of results doesn't matter.

Example:
If the Logs table contains: [1,1,1,2,1,1,2,2], then number 1 appears three times consecutively (rows 1-3), so it should be included in the result.

Input & Output

example_1.sql — Basic Consecutive Pattern

$ Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+

› Output: Result table: +-----------------+ | ConsecutiveNum | +-----------------+ | 1 | +-----------------+

💡 Note: Number '1' appears in rows 1, 2, and 3 consecutively, forming a streak of exactly 3. Number '2' only appears consecutively in rows 6-7 (streak of 2), which doesn't meet our requirement of at least 3.

example_2.sql — Multiple Consecutive Patterns

$ Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | | 6 | 2 | | 7 | 2 | +----+-----+

› Output: Result table: +-----------------+ | ConsecutiveNum | +-----------------+ | 1 | | 2 | +-----------------+

💡 Note: Both numbers qualify: '1' appears consecutively in rows 1-3 (streak of 3), and '2' appears consecutively in rows 4-7 (streak of 4). Both meet the minimum requirement of 3 consecutive appearances.

example_3.sql — No Consecutive Pattern

$ Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 2 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | +----+-----+

› Output: Result table: +-----------------+ | ConsecutiveNum | +-----------------+

💡 Note: No number appears 3 times consecutively. The pattern alternates between 1 and 2, so the maximum consecutive count for any number is 1. The result table is empty.

Visualization

Tap to expand

Understanding the Visualization

Position Window

Place a 3-element window starting at the first row

Compare Values

Check if all three values in the window are identical

Slide Window

Move window one position forward and repeat

Collect Results

Record any number that creates a 3+ consecutive match

Key Takeaway

🎯 Key Insight: Window functions eliminate the need for expensive self-joins by allowing us to access neighboring rows directly, making consecutive pattern detection both elegant and efficient.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table with window function

✓ Linear Growth

Space Complexity

O(1)

Window functions use constant extra space

✓ Linear Space

Constraints

1 ≤ Logs table size ≤ 10⁴
id is a unique auto-increment integer starting from 1
num is stored as varchar but contains numeric values
Consecutive means the IDs must be sequential (no gaps)
Result should contain unique numbers only (use DISTINCT)

Asked in

f Meta 45 a Amazon 35 ⊞ Microsoft 28 G Google 22

The optimal solution uses window functions with LAG() to compare each row with its previous two consecutive rows in a single pass. This eliminates the need for expensive self-joins and provides O(n) time complexity. The key insight is that window functions allow us to 'look back' or 'look ahead' in ordered data without creating cartesian products.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function Approach (Optimal)	O(n)	O(1)	Use LAG window function to compare each row with next two rows in one pass
Brute Force (Self Joins)	O(n²)	O(n²)	Use multiple self joins to check every consecutive triplet manually

Window Function Approach (Optimal) — Algorithm Steps

Use LAG() window function to get values from next 2 consecutive rows
Compare current row's num with LAG(-1) and LAG(-2) values
Filter rows where all three consecutive values match
Select distinct numbers from the filtered results

Visualization

Tap to expand

Step-by-Step Walkthrough

Apply Window Function

LAG() looks back at previous rows

Create Comparison Columns

Each row now has current, prev1, prev2 values

Filter Matches

Keep rows where all three values are identical

Code -

solution.c — C

// C string implementation
#include <stdio.h>

char* solution() {
    static char query[] = 
        "SELECT DISTINCT num as ConsecutiveNum "
        "FROM ( "
        "    SELECT num, "
        "           LAG(num, 1) OVER (ORDER BY id) as prev1, "
        "           LAG(num, 2) OVER (ORDER BY id) as prev2 "
        "    FROM Logs "
        ") t "
        "WHERE num = prev1 AND num = prev2;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table with window function

✓ Linear Growth

Space Complexity

O(1)

Window functions use constant extra space

✓ Linear Space

Constraints

1 ≤ Logs table size ≤ 10⁴
id is a unique auto-increment integer starting from 1
num is stored as varchar but contains numeric values
Consecutive means the IDs must be sequential (no gaps)
Result should contain unique numbers only (use DISTINCT)

28.4K Views

High Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Function Approach (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler