LeetCode SQL - CTE

Posted on Edited on

LAG & LEAD

180. Consecutive Numbers

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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');

Dedicated Solution.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/* 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

1
2
3
4
5
6
7
8
9
10
11
12
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

1709. Biggest Window Between Visits

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DROP TABLE if EXISTS UserVisits;
CREATE TABLE
    If NOT EXISTS UserVisits (user_id INT, visit_date DATE);
TRUNCATE TABLE UserVisits;
INSERT INTO
    UserVisits (user_id, visit_date)
VALUES
    ('1', '2020-11-28');
INSERT INTO
    UserVisits (user_id, visit_date)
VALUES
    ('1', '2020-10-20');
INSERT INTO
    UserVisits (user_id, visit_date)
VALUES
    ('1', '2020-12-3');
INSERT INTO
    UserVisits (user_id, visit_date)
VALUES
    ('2', '2020-10-5');
INSERT INTO
    UserVisits (user_id, visit_date)
VALUES
    ('2', '2020-12-9');
INSERT INTO
    UserVisits (user_id, visit_date)
VALUES
    ('3', '2020-11-11');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH
    cte AS (
        SELECT
            user_id,
            visit_date,
            lead (visit_date) OVER (
                PARTITION BY
                    user_id
                ORDER BY
                    visit_date
            ) AS date_lead
        FROM
            UserVisits
    )
SELECT
    user_id,
    MAX(datediff (ifnull (date_lead, '2021-1-1'), visit_date)) AS biggest_window
FROM
    cte
GROUP BY
    user_id;

2142. The Number of Passengers in Each Bus I

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DROP TABLE if EXISTS Buses;
DROP TABLE if EXISTS Passengers;
CREATE TABLE
    If NOT EXISTS Buses (bus_id INT, arrival_time INT);
CREATE TABLE
    If NOT EXISTS Passengers (passenger_id INT, arrival_time INT);
TRUNCATE TABLE Buses;
INSERT INTO
    Buses (bus_id, arrival_time)
VALUES
    ('1', '2');
INSERT INTO
    Buses (bus_id, arrival_time)
VALUES
    ('2', '4');
INSERT INTO
    Buses (bus_id, arrival_time)
VALUES
    ('3', '7');
TRUNCATE TABLE Passengers;
INSERT INTO
    Passengers (passenger_id, arrival_time)
VALUES
    ('11', '1');
INSERT INTO
    Passengers (passenger_id, arrival_time)
VALUES
    ('12', '5');
INSERT INTO
    Passengers (passenger_id, arrival_time)
VALUES
    ('13', '6');
INSERT INTO
    Passengers (passenger_id, arrival_time)
VALUES
    ('14', '7');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
WITH
    cte AS (
        SELECT
            bus_id,
            arrival_time,
            ifnull (lag (arrival_time) OVER (), 0) AS previous_time
        FROM
            (
                SELECT
                    *
                FROM
                    Buses
                ORDER BY
                    2
            ) b
    )
SELECT
    bus_id,
    COUNT(passenger_id) AS passengers_cnt
FROM
    cte
    LEFT JOIN Passengers p ON p.arrival_time BETWEEN cte.previous_time AND cte.arrival_time
GROUP BY
    1
ORDER BY
    1;

SUM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
DROP TABLE if EXISTS Scores;
CREATE TABLE
    If NOT EXISTS Scores (player_name VARCHAR(20), gender VARCHAR(1), DAY DATE, score_points INT);
TRUNCATE TABLE Scores;
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Aron', 'F', '2020-01-01', '17');
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Alice', 'F', '2020-01-07', '23');
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Bajrang', 'M', '2020-01-07', '7');
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Khali', 'M', '2019-12-25', '11');
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Slaman', 'M', '2019-12-30', '13');
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Joe', 'M', '2019-12-31', '3');
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Jose', 'M', '2019-12-18', '2');
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Priya', 'F', '2019-12-31', '23');
INSERT INTO
    Scores (player_name, gender, DAY, score_points)
VALUES
    ('Priyanka', 'F', '2019-12-30', '17');
1
2
3
4
5
6
7
8
9
10
11
SELECT
    gender,
    DAY,
    SUM(score_points) OVER (
        PARTITION BY
            gender
        ORDER BY
            DAY
    ) AS total
FROM
    Scores;

2004. The Number of Seniors and Juniors to Join the Company

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DROP TABLE if EXISTS Candidates;
CREATE TABLE
    If NOT EXISTS Candidates (employee_id INT, experience ENUM ('Senior', 'Junior'), salary INT);
TRUNCATE TABLE Candidates;
INSERT INTO
    Candidates (employee_id, experience, salary)
VALUES
    ('1', 'Junior', '10000');
INSERT INTO
    Candidates (employee_id, experience, salary)
VALUES
    ('9', 'Junior', '10000');
INSERT INTO
    Candidates (employee_id, experience, salary)
VALUES
    ('2', 'Senior', '20000');
INSERT INTO
    Candidates (employee_id, experience, salary)
VALUES
    ('11', 'Senior', '20000');
INSERT INTO
    Candidates (employee_id, experience, salary)
VALUES
    ('13', 'Senior', '50000');
INSERT INTO
    Candidates (employee_id, experience, salary)
VALUES
    ('4', 'Junior', '40000');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
WITH
    cte AS (
        SELECT
            employee_id,
            experience,
            -- salary,
            SUM(salary) OVER (
                PARTITION BY
                    experience
                ORDER BY
                    salary,
                    employee_id asc
            ) AS total_salary
        FROM
            Candidates
    ),
    max_senior_salary AS (
        SELECT
            ifnull (MAX(total_salary), 0) AS max_senior_salary
        FROM
            cte
        WHERE
            experience = 'Senior'
            AND total_salary <= 70000
    )
SELECT
    'Senior' AS experience,
    COUNT(*) AS accepted_candidates
FROM
    cte
WHERE
    experience = 'Senior'
    AND total_salary <= 70000
UNION ALL
SELECT
    'Junior' AS experience,
    COUNT(*) AS accepted_candidates
FROM
    cte
WHERE
    experience = 'Junior'
    AND total_salary <= 70000 - (
        SELECT
            max_senior_salary
        FROM
            max_senior_salary
    );

WITH RECURSIVE

1270. All People Report to the Given Manager

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DROP TABLE IF EXISTS Employees;
CREATE TABLE
    If NOT EXISTS Employees (employee_id INT, employee_name VARCHAR(30), manager_id INT);
TRUNCATE TABLE Employees;
INSERT INTO
    Employees (employee_id, employee_name, manager_id)
VALUES
    ('1', 'Boss', '1');
INSERT INTO
    Employees (employee_id, employee_name, manager_id)
VALUES
    ('3', 'Alice', '3');
INSERT INTO
    Employees (employee_id, employee_name, manager_id)
VALUES
    ('2', 'Bob', '1');
INSERT INTO
    Employees (employee_id, employee_name, manager_id)
VALUES
    ('4', 'Daniel', '2');
INSERT INTO
    Employees (employee_id, employee_name, manager_id)
VALUES
    ('7', 'Luis', '4');
INSERT INTO
    Employees (employee_id, employee_name, manager_id)
VALUES
    ('8', 'John', '3');
INSERT INTO
    Employees (employee_id, employee_name, manager_id)
VALUES
    ('9', 'Angela', '8');
INSERT INTO
    Employees (employee_id, employee_name, manager_id)
VALUES
    ('77', 'Robert', '1');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
/* Select the employees that report to the head.

- Employees PK: employee_id

- SOLUTION: WITH RECURSIVE; UNION ALL
 */
WITH RECURSIVE
    cte (employee_id, manager_id) AS (
        SELECT
            employee_id,
            CAST(manager_id AS CHAR(30))
        FROM
            Employees
        WHERE
            employee_id = 1
        UNION ALL
        SELECT
            e.employee_id,
            concat (e.employee_id, '-->', c.manager_id)
        FROM
            Employees e
            INNER JOIN cte c ON e.manager_id = c.employee_id
        WHERE
            e.employee_id != 1
    )
SELECT
    employee_id
FROM
    cte
WHERE
    employee_id != 1;

1164. Product Price at a Given Date

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
DROP TABLE IF EXISTS Products;
CREATE TABLE
    If NOT EXISTS Products (product_id INT, new_price INT, change_date DATE);
TRUNCATE TABLE Products;
INSERT INTO
    Products (product_id, new_price, change_date)
VALUES
    ('1', '20', '2019-08-14');
INSERT INTO
    Products (product_id, new_price, change_date)
VALUES
    ('2', '50', '2019-08-14');
INSERT INTO
    Products (product_id, new_price, change_date)
VALUES
    ('1', '30', '2019-08-15');
INSERT INTO
    Products (product_id, new_price, change_date)
VALUES
    ('1', '35', '2019-08-16');
INSERT INTO
    Products (product_id, new_price, change_date)
VALUES
    ('2', '65', '2019-08-17');
INSERT INTO
    Products (product_id, new_price, change_date)
VALUES
    ('3', '20', '2019-08-18');```

```sql
/*Select the product price at a given date

- Products PK: (product_id, change_date)

- SOLUTION: window function, UNION ALL
 */
WITH
    cte AS (
        SELECT
            product_id,
            MAX(change_date) AS change_date
        FROM
            Products
        WHERE
            change_date <= '2019-08-16'
        GROUP BY
            product_id
    )
SELECT
    cte.product_id,
    new_price AS price
FROM
    cte
    LEFT JOIN Products USING (product_id, change_date)
UNION ALL
SELECT DISTINCT
    product_id,
    10
FROM
    Products
WHERE
    product_id NOT IN (
        SELECT
            product_id
        FROM
            cte
    );

615. Average Salary: Departments VS Company

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DROP TABLE IF EXISTS salary;
DROP TABLE IF EXISTS employee;
CREATE TABLE
    If NOT EXISTS salary (id INT, employee_id INT, amount INT, pay_date DATE);
CREATE TABLE
    If NOT EXISTS employee (employee_id INT, department_id INT);
TRUNCATE TABLE salary;
INSERT INTO
    salary (id, employee_id, amount, pay_date)
VALUES
    ('1', '1', '9000', '2017/03/31');
INSERT INTO
    salary (id, employee_id, amount, pay_date)
VALUES
    ('2', '2', '6000', '2017/03/31');
INSERT INTO
    salary (id, employee_id, amount, pay_date)
VALUES
    ('3', '3', '10000', '2017/03/31');
INSERT INTO
    salary (id, employee_id, amount, pay_date)
VALUES
    ('4', '1', '7000', '2017/02/28');
INSERT INTO
    salary (id, employee_id, amount, pay_date)
VALUES
    ('5', '2', '6000', '2017/02/28');
INSERT INTO
    salary (id, employee_id, amount, pay_date)
VALUES
    ('6', '3', '8000', '2017/02/28');
TRUNCATE TABLE employee;
INSERT INTO
    employee (employee_id, department_id)
VALUES
    ('1', '1');
INSERT INTO
    employee (employee_id, department_id)
VALUES
    ('2', '2');
INSERT INTO
    employee (employee_id, department_id)
VALUES
    ('3', '2');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
/*Compare the department average amount to the company average amount
- salary PK: id

- SOLUTION: Calculate the department average and the company average amount,
            Then compare them.
*/
WITH RECURSIVE
company_avg AS
	(
	SELECT
		DATE_FORMAT(s.pay_date, '%Y-%m') AS pay_month,
		AVG(s.amount) AS company_avg
	FROM salary s
	LEFT JOIN employee e
		ON s.employee_id = e.employee_id
	GROUP BY pay_month
    ),
department_avg AS
	(
	SELECT
		DATE_FORMAT(s.pay_date, '%Y-%m') AS pay_month,
		e.department_id,
		AVG(s.amount) AS department_avg
	FROM salary s
	LEFT JOIN employee e
		ON s.employee_id = e.employee_id
	GROUP BY e.department_id, pay_month
	)
SELECT
	d.department_id,
    d.pay_month,
    CASE
		WHEN department_avg = company_avg THEN 'same'
        WHEN department_avg > company_avg THEN 'higher'
        WHEN department_avg < company_avg THEN 'lower'
	END AS comparison
FROM department_avg d
LEFT JOIN company_avg c
	ON d.pay_month = c.pay_month;

1336. Number of Transactions per Visit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
DROP TABLE IF EXISTS Visits;
DROP TABLE IF EXISTS Transactions;
CREATE TABLE
    If NOT EXISTS Visits (user_id INT, visit_date DATE);
CREATE TABLE
    If NOT EXISTS Transactions (user_id INT, transaction_date DATE, amount INT);
TRUNCATE TABLE Visits;
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('1', '2020-01-01');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('2', '2020-01-02');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('12', '2020-01-01');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('19', '2020-01-03');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('1', '2020-01-02');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('2', '2020-01-03');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('1', '2020-01-04');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('7', '2020-01-11');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('9', '2020-01-25');
INSERT INTO
    Visits (user_id, visit_date)
VALUES
    ('8', '2020-01-28');
TRUNCATE TABLE Transactions;
INSERT INTO
    Transactions (user_id, transaction_date, amount)
VALUES
    ('1', '2020-01-02', '120');
INSERT INTO
    Transactions (user_id, transaction_date, amount)
VALUES
    ('2', '2020-01-03', '22');
INSERT INTO
    Transactions (user_id, transaction_date, amount)
VALUES
    ('7', '2020-01-11', '232');
INSERT INTO
    Transactions (user_id, transaction_date, amount)
VALUES
    ('1', '2020-01-04', '7');
INSERT INTO
    Transactions (user_id, transaction_date, amount)
VALUES
    ('9', '2020-01-25', '33');
INSERT INTO
    Transactions (user_id, transaction_date, amount)
VALUES
    ('9', '2020-01-25', '66');
INSERT INTO
    Transactions (user_id, transaction_date, amount)
VALUES
    ('8', '2020-01-28', '1');
INSERT INTO
    Transactions (user_id, transaction_date, amount)
VALUES
    ('9', '2020-01-25', '99');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/*Number of Transactions per Visit
- Visits PK: (user_id, visit_date)
- Transactions PK: NULL

- SOLUTION:
*/
WITH RECURSIVE
# count the amount and distinct visitors
transaction_visit AS
	(
	SELECT
		v.user_id,
		v.visit_date,
		COUNT(DISTINCT v.user_id) AS count_user_id, # the unique visit
		COUNT(t.amount) AS count_amount
	FROM Visits v
	LEFT JOIN Transactions t
		ON v.user_id = t.user_id
		AND v.visit_date = t.transaction_date
	GROUP BY v.user_id, v.visit_date
    ),
# result
transactions_per_visit AS
	(
	SELECT
		count_amount AS transactions_count,
		SUM(count_user_id) AS visits_count
	FROM transaction_visit
	GROUP BY count_amount
    ),
# to fill in the transactions_per_visit
full_transactions AS
	(
	SELECT 0 AS transactions_count
    UNION ALL
    SELECT transactions_count + 1
    from full_transactions
    WHERE transactions_count < (SELECT MAX(transactions_count) FROM transactions_per_visit)
	)
SELECT f.transactions_count, IFNULL(t.visits_count, 0) AS visits_count
FROM full_transactions f
LEFT JOIN transactions_per_visit t
	USING(transactions_count);

I think the correct SQL query should be:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH CTE AS
	(
	SELECT
		v.user_id,
		v.visit_date,
		COUNT(DISTINCT v.user_id) AS count_user_id, # the unique visit
		COUNT(t.amount) AS count_amount
	FROM Visits v
	LEFT JOIN Transactions t
		ON v.user_id = t.user_id
		AND v.visit_date = t.transaction_date
	GROUP BY v.user_id, v.visit_date
    )
-- SELECT * FROM CTE;
SELECT
	count_amount,
	SUM(count_user_id) AS visits_count
FROM CTE
GROUP BY count_amount;