LeetCode SQL - Consecutive Records

Open Table of contents

Identify Consecutive Records
Generate Consecutive Records
GENERATE_SERIES

Identify Consecutive Records

1811. Find Interview Candidates

WITH medals (contest_id, user_id) AS (
    SELECT contest_id, gold_medal FROM Contests
    UNION ALL
    SELECT contest_id, silver_medal FROM Contests
    UNION ALL
    SELECT contest_id, bronze_medal FROM Contests
),
grps AS (
    SELECT
        user_id,
        contest_id,
        contest_id - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY contest_id) AS grp
    FROM medals
),
winners (user_id) AS (
    SELECT DISTINCT user_id
    FROM grps
    GROUP BY
        user_id,
        grp
    HAVING COUNT(contest_id) >= 3
    UNION
    SELECT gold_medal
    FROM Contests
    GROUP BY gold_medal
    HAVING COUNT(gold_medal) >= 3
)
SELECT
    name,
    mail
FROM
    winners
    JOIN Users USING (user_id)

1454. Active Users

WITH cte AS (
	SELECT
		id,
		login_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
			PARTITION BY id ORDER BY login_date
		) AS grp
	FROM (SELECT DISTINCT id, login_date FROM Logins)
)
SELECT DISTINCT
    cte.id,
    a.name
FROM
    cte
    JOIN Accounts a ON cte.id = a.id
GROUP BY
    cte.id,
    a.name,
    grp
HAVING COUNT(grp) >= 5
ORDER BY cte.id

1285. Find the Start and End Number of Continuous Ranges

WITH cte AS (
	SELECT
		log_id,
		log_id - ROW_NUMBER() OVER (ORDER BY log_id) AS grp
	FROM Logs
)
SELECT
    MIN(log_id) AS start_id,
    MAX(log_id) AS end_id
FROM cte
GROUP BY grp
ORDER BY start_id

1225. Report Contiguous Dates

WITH events (event_date, state) AS (
	SELECT fail_date, 'failed' FROM Failed
	UNION
	SELECT success_date, 'succeeded' FROM Succeeded
),
grps AS (
	SELECT
		state,
		event_date,
		event_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
		PARTITION BY state ORDER BY event_date
		) AS grp
	FROM events
	WHERE event_date BETWEEN '2019-01-01'::DATE AND '2019-12-31'::DATE
)
SELECT
    state AS period_state,
    MIN(event_date) AS start_date,
    MAX(event_date) AS end_date
FROM grps
GROUP BY
    period_state,
    grp
ORDER BY start_date

603. Consecutive Available Seats

Hardcoded solution

WITH seat_analysis AS (
    SELECT
        seat_id,
        free,
        LAG(free) OVER (ORDER BY seat_id) AS prev_free,
        LEAD(free) OVER (ORDER BY seat_id) AS next_free
    FROM Cinema
)
SELECT seat_id
FROM seat_analysis
WHERE 
	free = 1
    AND (prev_free = 1 OR next_free = 1)
ORDER BY seat_id

Universal solution

WITH grps AS (
    SELECT
        seat_id,
        seat_id - ROW_NUMBER() OVER (ORDER BY seat_id) AS grp
    FROM Cinema
    WHERE free = 1
),
seat_counts AS (
    SELECT
        seat_id,
        COUNT(seat_id) OVER (PARTITION BY grp) AS grp_size
    FROM grps
)
SELECT seat_id
FROM seat_counts
WHERE grp_size >= 2
ORDER BY seat_id

601. Human Traffic of Stadium

WITH grps AS (
    SELECT
        *,
        id - ROW_NUMBER() OVER (ORDER BY visit_date) AS grp
    FROM Stadium
    WHERE people >= 100
),
date_count AS (
    SELECT
        *,
        COUNT(id) OVER (PARTITION BY grp) AS grp_size
    FROM grps
)
SELECT
    id,
    visit_date,
    people
FROM date_count
WHERE grp_size >= 3
ORDER BY visit_date

Generate Consecutive Records

Same ID we can count for group by.

1613. Find the Missing IDs

SELECT s.ids
FROM 
    GENERATE_SERIES(1, (SELECT MAX(customer_id) FROM Customers)) AS s(ids)
    LEFT JOIN Customers c on s.ids = c.customer_id
WHERE c.customer_id IS NULL
ORDER BY ids

WITH RECURSIVE cte AS (
    SELECT 1 AS ids
    UNION
    SELECT ids + 1
    FROM cte
    WHERE ids < (SELECT MAX(customer_id) FROM Customers)
)
SELECT cte.ids
FROM
    cte
    LEFT JOIN Customers c on cte.ids = c.customer_id
WHERE c.customer_id IS NULL
ORDER BY ids

180. Consecutive Numbers

Dedicated Solution.

WITH cte AS (
    SELECT
        num,
        LAG(num, 1) OVER (ORDER BY 1) AS lag_1,
        LAG(num, 2) OVER (ORDER BY 1) AS lag_2
    FROM
        logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM cte
WHERE
    num = lag_1
    AND num = lag_2

Universal Solution: Universal Solution for All Consecutive Cases

WITH cte AS (
    SELECT 
        *,
        id - ROW_NUMBER() OVER(PARTITION BY num ORDER BY id ASC) AS grp
    FROM Logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM cte
GROUP BY
	num,
	grp
HAVING COUNT(num) >= 3

1767. Find the Subtasks That Did Not Execute

WITH RECURSIVE

WITH RECURSIVE cte (task_id, subtask_id) AS (
	SELECT task_id, 1
	FROM Tasks
	UNION
	SELECT
		c.task_id,
		c.subtask_id + 1
	FROM
		cte c
		JOIN Tasks t USING (task_id)
	WHERE c.subtask_id < t.subtasks_count
)
SELECT
    c.task_id,
    c.subtask_id
FROM
    cte c
    LEFT JOIN Executed e ON c.task_id = e.task_id
	    AND c.subtask_id = e.subtask_id
WHERE e.subtask_id IS NULL
ORDER BY task_id

GENERATE_SERIES: Postgresql exclusive

SELECT
    t.task_id,
    s.subtask_id
FROM
    Tasks t
    CROSS JOIN GENERATE_SERIES(1, t.subtasks_count) AS s(subtask_id)
    LEFT JOIN Executed e ON t.task_id = e.task_id
	    AND s.subtask_id = e.subtask_id
WHERE e.subtask_id IS NULL
ORDER BY t.task_id

GENERATE_SERIES

Basic Syntax

GENERATE_SERIES(START, STOP)
GENERATE_SERIES(START, STOP, STEP)

Simple Examples

Example 1: Generate Numbers 1 to 5

SELECT * FROM generate_series(1, 5);

Output:

 generate_series
-----------------
               1
               2
               3
               4
               5

Example 2: With Step

SELECT * FROM generate_series(1, 10, 2);

Output:

 generate_series
-----------------
               1
               3
               5
               7
               9

Example 3: Countdown

SELECT * FROM generate_series(10, 1, -1);

Output:

 generate_series
-----------------
              10
               9
               8
               ...
               1

Using with Tables (CROSS JOIN)

SELECT
    t.task_id,
    s.subtask_id
FROM
	Tasks t
	CROSS JOIN generate_series(1, t.subtasks_count) AS s(subtask_id)
	LEFT JOIN Executed e ON t.task_id = e.task_id 
	    AND s.subtask_id = e.subtask_id
WHERE e.subtask_id IS NULL
ORDER BY t.task_id;

| task_id | subtask_id |
| ------- | ---------- |
| 1       | 1          |
| 1       | 2          |
| 1       | 3          |
| 2       | 1          |
| 2       | 2          |
| 3       | 1          |
| 3       | 2          |
| 3       | 3          |
| 3       | 4          |

Date/Time Series

Example 1: Generate Date Range

SELECT generate_series(
    '2024-01-01'::date,
    '2024-01-07'::date,
    '1 day'::interval
) AS date;

Output:

    date
------------
2024-01-01
2024-01-02
2024-01-03
2024-01-04
2024-01-05
2024-01-06
2024-01-07

Example 2: Every Hour in a Day

SELECT generate_series(
    '2024-01-01 00:00:00'::timestamp,
    '2024-01-01 23:00:00'::timestamp,
    '1 hour'::interval
) AS hour;

Example 3: Generate Monthly Dates

SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-01'::date,
    '1 month'::interval
) AS month;

Output:

   month
------------
2024-01-01
2024-02-01
2024-03-01
...
2024-12-01

Common Use Cases

Use Case 1: Fill Missing Dates in Time Series

Problem: Sales data with gaps, need to show all dates with 0 for missing days.

WITH date_range AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-01-31'::date,
        '1 day'::interval
    )::date AS date
)
SELECT
    dr.date,
    COALESCE(SUM(s.amount), 0) AS total_sales
FROM date_range dr
LEFT JOIN sales s ON dr.date = s.sale_date
GROUP BY dr.date
ORDER BY dr.date;

Output:

    date    | total_sales
------------+-------------
2024-01-01 |         100
2024-01-02 |           0  ← Missing day filled
2024-01-03 |         250
...

Use Case 2: Generate All Hours for Each User

WITH hours AS (
    SELECT generate_series(0, 23) AS hour
)
SELECT
    u.user_id,
    h.hour,
    COALESCE(COUNT(a.activity_id), 0) AS activity_count
FROM users u
CROSS JOIN hours h
LEFT JOIN activities a 
    ON u.user_id = a.user_id 
    AND EXTRACT(HOUR FROM a.timestamp) = h.hour
GROUP BY u.user_id, h.hour
ORDER BY u.user_id, h.hour;

Use Case 3: Create Cartesian Product (All Combinations)

-- Generate all possible (product, store) combinations
SELECT
    p.product_id,
    s.store_id
FROM products p
CROSS JOIN stores s;

-- With generate_series: All products × first 10 days
SELECT
    p.product_id,
    d.day
FROM products p
CROSS JOIN generate_series(1, 10) AS d(day);

Use Case 4: Split String into Rows

-- Get each character position in a string
SELECT
    s.pos,
    SUBSTRING('Hello' FROM s.pos FOR 1) AS char
FROM generate_series(1, LENGTH('Hello')) AS s(pos);

Output:

 pos | char
-----+------
   1 | H
   2 | e
   3 | l
   4 | l
   5 | o

Use Case 5: Pagination/Batch Processing

-- Generate page numbers for pagination
SELECT * FROM generate_series(1, 10) AS page_number;

-- Generate batch IDs
SELECT 
    batch_id,
    batch_id * 1000 AS start_id,
    (batch_id + 1) * 1000 AS end_id
FROM generate_series(0, 9) AS batch_id;

Output:

 batch_id | start_id | end_id
----------+----------+--------
        0 |        0 |   1000
        1 |     1000 |   2000
        ...

Advanced Patterns

Pattern 1: Nested generate_series (Multiplication Table)

SELECT
    x.n AS x,
    y.n AS y,
    x.n * y.n AS product
FROM generate_series(1, 5) AS x(n)
CROSS JOIN generate_series(1, 5) AS y(n);

Output:

 x | y | product
---+---+---------
 1 | 1 |       1
 1 | 2 |       2
 1 | 3 |       3
 ...
 5 | 5 |      25

Pattern 2: Generate Test Data

-- Generate 1000 test users
INSERT INTO users (user_id, user_name, created_at)
SELECT
    n AS user_id,
    'User_' || n AS user_name,
    NOW() - (n || ' days')::interval AS created_at
FROM generate_series(1, 1000) AS n;

Pattern 3: Fill Gaps in Sequences

-- Find missing IDs in a sequence
WITH all_ids AS (
    SELECT generate_series(1, 100) AS id
)
SELECT a.id AS missing_id
FROM all_ids a
LEFT JOIN users u ON a.id = u.user_id
WHERE u.user_id IS NULL;

Pattern 4: Running Totals with All Dates

WITH date_range AS (
    SELECT generate_series(
        DATE_TRUNC('month', CURRENT_DATE),
        CURRENT_DATE,
        '1 day'::interval
    )::date AS date
)
SELECT
    dr.date,
    COALESCE(SUM(s.amount) OVER (ORDER BY dr.date), 0) AS running_total
FROM date_range dr
LEFT JOIN sales s ON dr.date = s.sale_date
ORDER BY dr.date;

Comparison: generate_series vs Recursive CTE

For LeetCode 1767:

generate_series (Simpler):

SELECT
    t.task_id,
    s.subtask_id
FROM Tasks t
CROSS JOIN generate_series(1, t.subtasks_count) AS s(subtask_id)
LEFT JOIN Executed e 
    ON t.task_id = e.task_id 
    AND s.subtask_id = e.subtask_id
WHERE e.subtask_id IS NULL;

Recursive CTE (Portable):

WITH RECURSIVE cte AS (
    SELECT task_id, 1 AS subtask_id FROM Tasks
    UNION
    SELECT c.task_id, c.subtask_id + 1
    FROM cte c
    JOIN Tasks t ON c.task_id = t.task_id
    WHERE c.subtask_id < t.subtasks_count
)
SELECT c.task_id, c.subtask_id
FROM cte c
LEFT JOIN Executed e 
    ON c.task_id = e.task_id 
    AND c.subtask_id = e.subtask_id
WHERE e.subtask_id IS NULL;

Aspect	generate_series	Recursive CTE
Lines of code	Fewer	More
Readability	Higher	Medium
Performance	Faster	Slightly slower
Portability	PostgreSQL only	Standard SQL
Flexibility	Limited to sequences	Very flexible

Common Mistakes

Mistake 1: Forgetting to Alias

-- ❌ Error: subquery must have an alias
SELECT * FROM generate_series(1, 5);

-- ✅ Correct
SELECT * FROM generate_series(1, 5) AS n;
-- Or if you want a column name:
SELECT * FROM generate_series(1, 5) AS s(num);

Mistake 2: Wrong Date Interval Syntax

-- ❌ Wrong
generate_series('2024-01-01', '2024-01-31', 1)

-- ✅ Correct
generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day'::interval)

Mistake 3: Inclusive End

-- generate_series is INCLUSIVE on both ends
SELECT * FROM generate_series(1, 5);
-- Returns: 1, 2, 3, 4, 5 (includes 5!)

-- If you want exclusive end:
SELECT * FROM generate_series(1, 5 - 1);
-- Returns: 1, 2, 3, 4

Database Compatibility

PostgreSQL: ✅ Native Support

SELECT * FROM generate_series(1, 10);

MySQL: ❌ No Native Support

-- Must use recursive CTE or a numbers table
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

SQL Server: ✅ Similar Function

-- SQL Server uses different syntax
SELECT value FROM generate_series(1, 10);
-- Or older versions:
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.objects;

SQLite: ❌ No Native Support

-- Must create a numbers table or use recursive CTE

Performance Tips

Tip 1: Use for Small Ranges

-- ✅ Good: Generate 1-1000
SELECT * FROM generate_series(1, 1000);

-- ⚠️ Caution: Generate 1-1,000,000
-- Consider if you really need all rows
SELECT * FROM generate_series(1, 1000000);

Tip 2: Index Your Join Columns

-- If joining with generate_series often:
CREATE INDEX idx_tasks_subtasks ON Tasks(task_id, subtasks_count);

Tip 3: Use WITH Materialization

-- For complex queries, materialize the series:
WITH MATERIALIZED date_range AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-12-31'::date,
        '1 day'::interval
    )::date AS date
)
SELECT * FROM date_range ...;

Summary

generate_series is perfect for:

✅ Generating numeric sequences
✅ Creating date/time ranges
✅ Filling gaps in data
✅ Creating test data
✅ Cartesian products with controlled size

Use Recursive CTE instead when:

❌ Need portability to other databases
❌ More complex generation logic
❌ Hierarchical data