LeetCode SQL - Consecutive Records

Same ID

Same ID we can count for group by.

1613. Find the Missing IDs

DROP TABLE IF EXISTS Customers;
CREATE TABLE
    If NOT EXISTS Customers (customer_id INT, customer_name VARCHAR(20));
TRUNCATE TABLE Customers;
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('37', 'Alice');
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('10', 'Bob');
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('29', 'Charlie');

/*Find continuous records
- Customers PK: log_id

- SOLUTION: window function, WITH RECURSIVE
 */
WITH RECURSIVE
    cte AS (
        SELECT
            1 AS ids
        UNION ALL
        SELECT
            ids + 1
        FROM
            cte
        WHERE
            ids < (
                SELECT
                    MAX(customer_id)
                FROM
                    Customers
            )
    )
SELECT
    ids
FROM
    cte
WHERE
    ids NOT IN (
        SELECT
            customer_id
        FROM
            Customers
    );

---

603. Consecutive Available Seats

DROP TABLE if EXISTS Cinema;
CREATE TABLE
    If NOT EXISTS Cinema (seat_id INT PRIMARY key auto_increment, FREE bool);
TRUNCATE TABLE Cinema;
INSERT INTO
    Cinema (seat_id, FREE)
VALUES
    ('1', '1');
INSERT INTO
    Cinema (seat_id, FREE)
VALUES
    ('2', '0');
INSERT INTO
    Cinema (seat_id, FREE)
VALUES
    ('3', '1');
INSERT INTO
    Cinema (seat_id, FREE)
VALUES
    ('4', '1');
INSERT INTO
    Cinema (seat_id, FREE)
VALUES
    ('5', '1');

SELECT DISTINCT
    c1.seat_id
FROM
    Cinema c1
    INNER JOIN Cinema c2 ON ABS(c1.seat_id - c2.seat_id) = 1
WHERE
    c1.free = 1
    AND c2.free = 1
ORDER BY
    1;

WITH
    cte AS (
        SELECT
            seat_id,
            FREE,
            lag (FREE, 1) OVER () AS free_lag,
            lead (FREE, 1) OVER () AS free_lead
        FROM
            Cinema
    )
SELECT
    seat_id
FROM
    cte
WHERE
    (
        FREE = 1
        AND free_lag = 1
    )
    OR (
        FREE = 1
        AND free_lead = 1
    )
ORDER BY
    1;

---

1811. Find Interview Candidates

DROP TABLE if EXISTS Contests;
DROP TABLE if EXISTS Users;
CREATE TABLE
    If NOT EXISTS Contests (contest_id INT, gold_medal INT, silver_medal INT, bronze_medal INT);
CREATE TABLE
    If NOT EXISTS Users (user_id INT, mail VARCHAR(50), name VARCHAR(30));
TRUNCATE TABLE Contests;
INSERT INTO
    Contests (contest_id, gold_medal, silver_medal, bronze_medal)
VALUES
    ('190', '1', '5', '2');
INSERT INTO
    Contests (contest_id, gold_medal, silver_medal, bronze_medal)
VALUES
    ('191', '2', '3', '5');
INSERT INTO
    Contests (contest_id, gold_medal, silver_medal, bronze_medal)
VALUES
    ('192', '5', '2', '3');
INSERT INTO
    Contests (contest_id, gold_medal, silver_medal, bronze_medal)
VALUES
    ('193', '1', '3', '5');
INSERT INTO
    Contests (contest_id, gold_medal, silver_medal, bronze_medal)
VALUES
    ('194', '4', '5', '2');
INSERT INTO
    Contests (contest_id, gold_medal, silver_medal, bronze_medal)
VALUES
    ('195', '4', '2', '1');
INSERT INTO
    Contests (contest_id, gold_medal, silver_medal, bronze_medal)
VALUES
    ('196', '1', '5', '2');
TRUNCATE TABLE Users;
INSERT INTO
    Users (user_id, mail, name)
VALUES
    ('1', 'sarah@leetcode.com', 'Sarah');
INSERT INTO
    Users (user_id, mail, name)
VALUES
    ('2', 'bob@leetcode.com', 'Bob');
INSERT INTO
    Users (user_id, mail, name)
VALUES
    ('3', 'alice@leetcode.com', 'Alice');
INSERT INTO
    Users (user_id, mail, name)
VALUES
    ('4', 'hercy@leetcode.com', 'Hercy');
INSERT INTO
    Users (user_id, mail, name)
VALUES
    ('5', 'quarz@leetcode.com', 'Quarz');

WITH
    medals AS (
        SELECT
            contest_id,
            gold_medal AS user_id
        FROM
            Contests
        UNION ALL
        SELECT
            contest_id,
            silver_medal AS user_id
        FROM
            Contests
        UNION ALL
        SELECT
            contest_id,
            bronze_medal AS user_id
        FROM
            Contests
    ),
    medals_by_users AS (
        SELECT
            user_id,
            contest_id,
            ROW_NUMBER() OVER (
                PARTITION BY
                    user_id
                ORDER BY
                    contest_id
            ) AS rn
        FROM
            medals
    ),
    user_ids AS (
        SELECT DISTINCT
            user_id
        FROM
            medals_by_users
        GROUP BY
            1,
            contest_id - rn
        HAVING
            COUNT(*) >= 3
        UNION
        SELECT DISTINCT
            gold_medal AS user_id
        FROM
            Contests
        GROUP BY
            1
        HAVING
            COUNT(gold_medal) >= 3
    )
SELECT
    `name`,
    mail
FROM
    Users
    INNER JOIN user_ids USING (user_id);

---

Different ID

Different ID we must count for window function.

---

1454. Active Users

DROP TABLE if EXISTS Accounts;
DROP TABLE if EXISTS Logins;
CREATE TABLE
    If NOT EXISTS Accounts (id INT, name VARCHAR(10));
CREATE TABLE
    If NOT EXISTS Logins (id INT, login_date DATE);
TRUNCATE TABLE Accounts;
INSERT INTO
    Accounts (id, name)
VALUES
    ('1', 'Winston');
INSERT INTO
    Accounts (id, name)
VALUES
    ('7', 'Jonathan');
TRUNCATE TABLE Logins;
INSERT INTO
    Logins (id, login_date)
VALUES
    ('7', '2020-05-30');
INSERT INTO
    Logins (id, login_date)
VALUES
    ('1', '2020-05-30');
INSERT INTO
    Logins (id, login_date)
VALUES
    ('7', '2020-05-31');
INSERT INTO
    Logins (id, login_date)
VALUES
    ('7', '2020-06-01');
INSERT INTO
    Logins (id, login_date)
VALUES
    ('7', '2020-06-02');
INSERT INTO
    Logins (id, login_date)
VALUES
    ('7', '2020-06-02');
INSERT INTO
    Logins (id, login_date)
VALUES
    ('7', '2020-06-03');
INSERT INTO
    Logins (id, login_date)
VALUES
    ('1', '2020-06-07');
INSERT INTO
    Logins (id, login_date)
VALUES
    ('7', '2020-06-10');

WITH
    cte AS (
        SELECT
            id,
            login_date,
            ROW_NUMBER() OVER (
                PARTITION BY
                    id
                ORDER BY
                    login_date
            ) AS rn
        FROM
            (
                SELECT DISTINCT
                    id,
                    login_date
                FROM
                    Logins
            ) AS distinct_logins
    )
SELECT DISTINCT
    cte.id,
    `name` -- avoid having same users
FROM
    cte
    INNER JOIN Accounts USING (id)
GROUP BY
    1,
    login_date - INTERVAL rn DAY -- avoid having same user with continusous but less than 5 records
HAVING
    COUNT(*) >= 5
ORDER BY
    1;

---

601. Human Traffic of Stadium

DROP TABLE if EXISTS Stadium;
CREATE TABLE
    If NOT EXISTS Stadium (id INT, visit_date DATE NULL, people INT);
TRUNCATE TABLE Stadium;
INSERT INTO
    Stadium (id, visit_date, people)
VALUES
    ('1', '2017-01-01', '10');
INSERT INTO
    Stadium (id, visit_date, people)
VALUES
    ('2', '2017-01-02', '109');
INSERT INTO
    Stadium (id, visit_date, people)
VALUES
    ('3', '2017-01-03', '150');
INSERT INTO
    Stadium (id, visit_date, people)
VALUES
    ('4', '2017-01-04', '99');
INSERT INTO
    Stadium (id, visit_date, people)
VALUES
    ('5', '2017-01-05', '145');
INSERT INTO
    Stadium (id, visit_date, people)
VALUES
    ('6', '2017-01-06', '1455');
INSERT INTO
    Stadium (id, visit_date, people)
VALUES
    ('7', '2017-01-07', '199');
INSERT INTO
    Stadium (id, visit_date, people)
VALUES
    ('8', '2017-01-09', '188');

WITH
    cte AS (
        SELECT
            *,
            id - ROW_NUMBER() OVER () AS diff
        FROM
            Stadium
        WHERE
            people >= 100
    ),
    cte1 AS (
        SELECT
            *,
            COUNT(*) OVER (
                PARTITION BY
                    diff
            ) AS n_records
        FROM
            cte
    )
SELECT
    id,
    visit_date,
    people
FROM
    cte1
WHERE
    n_records >= 3;

---

Consecutive Dates

1285. Find the Start and End Number of Continuous Ranges

DROP TABLE IF EXISTS Logs;
CREATE TABLE
    If NOT EXISTS Logs (log_id INT);
TRUNCATE TABLE Logs;
INSERT INTO
    Logs (log_id)
VALUES
    ('1');
INSERT INTO
    Logs (log_id)
VALUES
    ('2');
INSERT INTO
    Logs (log_id)
VALUES
    ('3');
INSERT INTO
    Logs (log_id)
VALUES
    ('7');
INSERT INTO
    Logs (log_id)
VALUES
    ('8');
INSERT INTO
    Logs (log_id)
VALUES
    ('10');

/*Find continuous records
- Logs PK: log_id

- SOLUTION: window function, ROW_NUMBER
 */
WITH
    cte AS (
        SELECT
            log_id,
            log_id - ROW_NUMBER() OVER () AS diff
        FROM
            Logs
    )
SELECT
    MIN(log_id) AS start_id,
    MAX(log_id) AS end_id
FROM
    cte
GROUP BY
    diff;

---

1225. Report Contiguous Dates

DROP TABLE IF EXISTS Failed;
DROP TABLE IF EXISTS Succeeded;
CREATE TABLE
    If NOT EXISTS Failed (fail_date DATE);
CREATE TABLE
    If NOT EXISTS Succeeded (success_date DATE);
TRUNCATE TABLE Failed;
INSERT INTO
    Failed (fail_date)
VALUES
    ('2018-12-28');
INSERT INTO
    Failed (fail_date)
VALUES
    ('2018-12-29');
INSERT INTO
    Failed (fail_date)
VALUES
    ('2019-01-04');
INSERT INTO
    Failed (fail_date)
VALUES
    ('2019-01-05');
TRUNCATE TABLE Succeeded;
INSERT INTO
    Succeeded (success_date)
VALUES
    ('2018-12-30');
INSERT INTO
    Succeeded (success_date)
VALUES
    ('2018-12-31');
INSERT INTO
    Succeeded (success_date)
VALUES
    ('2019-01-01');
INSERT INTO
    Succeeded (success_date)
VALUES
    ('2019-01-02');
INSERT INTO
    Succeeded (success_date)
VALUES
    ('2019-01-03');
INSERT INTO
    Succeeded (success_date)
VALUES
    ('2019-01-06');

/*Select the continuous records.
- Failed PK: NULL
- Succeeded PK: NULL

- SOLTUION: UNION ALL, BETWEEN, window function
 */
WITH
    cte (event_date, status) AS (
        SELECT
            fail_date,
            "failed"
        FROM
            Failed
        UNION
        SELECT
            success_date,
            "succeeded"
        FROM
            Succeeded
    ),
    cte1 AS (
        SELECT
            event_date,
            event_date - INTERVAL ROW_NUMBER() OVER (
                PARTITION BY
                    status
                ORDER BY
                    event_date
            ) DAY AS diff,
            status
        FROM
            cte
        WHERE
            event_date BETWEEN '2019-01-01' AND '2019-12-31'
    )
SELECT
    status AS period_state,
    MIN(event_date) AS start_date,
    MAX(event_date) AS end_date
FROM
    cte1
GROUP BY
    1,
    diff
ORDER BY
    2;

---

Consecutive Numbers/

180. Consecutive Numbers

DROP TABLE IF EXISTS Logs;
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);
INSERT INTO Logs (id, num)
VALUES (8, 3);
INSERT INTO Logs (id, num)
VALUES (9, 3);
INSERT INTO Logs (id, num)
VALUES (10, 4);
INSERT INTO Logs (id, num)
VALUES (11, 3);
INSERT INTO Logs (id, num)
VALUES (12, 3);

Dedicated Solution.

/* Select all numbers that appear at least three times consecutively.
- Logs PK: Id

- SOLUTION: Window Function
 */
WITH
    cte AS (
        SELECT
            num,
            lag (num, 1) OVER () AS lag_1,
            lag (num, 2) OVER () 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,
        num,
        id - CAST(RANK() OVER(PARTITION BY num ORDER BY id ASC) AS SIGNED) AS diff
    FROM Logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM cte
GROUP BY
    num,
    diff
HAVING COUNT(diff) >= 3