LeetCode SQL - Datetime

Posted on Edited on

Date

1384. Total Sales Amount by Year

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
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Sales;
CREATE TABLE
    If NOT EXISTS Product (product_id INT, product_name VARCHAR(30));
CREATE TABLE
    If NOT EXISTS Sales (
        product_id VARCHAR(30),
        period_start DATE,
        period_end DATE,
        average_daily_sales INT
    );
TRUNCATE TABLE Product;
INSERT INTO
    Product (product_id, product_name)
VALUES
    ('1', 'LC Phone ');
INSERT INTO
    Product (product_id, product_name)
VALUES
    ('2', 'LC T-Shirt');
INSERT INTO
    Product (product_id, product_name)
VALUES
    ('3', 'LC Keychain');
TRUNCATE TABLE Sales;
INSERT INTO
    Sales (product_id, period_start, period_end, average_daily_sales)
VALUES
    ('1', '2019-01-25', '2019-02-28', '100');
INSERT INTO
    Sales (product_id, period_start, period_end, average_daily_sales)
VALUES
    ('2', '2018-12-01', '2020-01-01', '10');
INSERT INTO
    Sales (product_id, period_start, period_end, average_daily_sales)
VALUES
    ('3', '2019-12-01', '2020-01-31', '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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
/*Select the total sales amount by year
- Product PK: product_id
- Sales PK: product_id

- SOLUTION: Create the sale_date range table then join to table Sales
 */
-- SET @start = (SELECT MIN(period_start) FROM Sales);
-- SET @end = (SELECT MAX(period_end) FROM Sales);
WITH RECURSIVE
    all_dates AS (
        SELECT
            MIN(period_start) AS sale_date
        FROM
            Sales
        UNION
        SELECT
            sale_date + INTERVAL 1 DAY
        FROM
            all_dates
        WHERE
            sale_date <= (
                SELECT
                    MAX(period_end)
                FROM
                    Sales
            )
    )
    -- select product_id, average_daily_sales
SELECT
    product_id,
    product_name,
    date_format (sale_date, '%Y') AS report_year,
    SUM(average_daily_sales) AS total_amount
FROM
    Sales
    INNER JOIN all_dates ON period_start <= sale_date
    AND sale_date <= period_end
    INNER JOIN Product USING (product_id)
GROUP BY
    1,
    2,
    3
ORDER BY
    1,
    2;

1107. New Users Daily Count

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
DROP TABLE if EXISTS Traffic;
CREATE TABLE
    If NOT EXISTS Traffic (
        user_id INT,
        activity ENUM ('login', 'logout', 'jobs', 'groups', 'homepage'),
        activity_date DATE
    );
TRUNCATE TABLE Traffic;
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('1', 'login', '2019-05-01');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('1', 'homepage', '2019-05-01');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('1', 'logout', '2019-05-01');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('2', 'login', '2019-06-21');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('2', 'logout', '2019-06-21');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('3', 'login', '2019-01-01');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('3', 'jobs', '2019-01-01');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('3', 'logout', '2019-01-01');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('4', 'login', '2019-06-21');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('4', 'groups', '2019-06-21');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('4', 'logout', '2019-06-21');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('5', 'login', '2019-03-01');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('5', 'logout', '2019-03-01');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('5', 'login', '2019-06-21');
INSERT INTO
    Traffic (user_id, activity, activity_date)
VALUES
    ('5', 'logout', '2019-06-21');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH
    cte AS (
        SELECT
            user_id,
            MIN(activity_date) AS first_login
        FROM
            Traffic
        WHERE
            activity = 'login'
        GROUP BY
            1
    )
SELECT
    first_login AS login_date,
    COUNT(user_id) AS user_count
FROM
    cte
WHERE
    datediff ('2019-06-30', first_login) <= 90
GROUP BY
    1
ORDER BY
    1;

1098. Unpopular Books

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
DROP TABLE if EXISTS Books;
DROP TABLE if EXISTS Orders;
CREATE TABLE
    If NOT EXISTS Books (book_id INT, name VARCHAR(50), available_from DATE);
CREATE TABLE
    If NOT EXISTS Orders (order_id INT, book_id INT, quantity INT, dispatch_date DATE);
TRUNCATE TABLE Books;
INSERT INTO
    Books (book_id, name, available_from)
VALUES
    ('1', 'Kalila And Demna', '2010-01-01');
INSERT INTO
    Books (book_id, name, available_from)
VALUES
    ('2', '28 Letters', '2012-05-12');
INSERT INTO
    Books (book_id, name, available_from)
VALUES
    ('3', 'The Hobbit', '2019-06-10');
INSERT INTO
    Books (book_id, name, available_from)
VALUES
    ('4', '13 Reasons Why', '2019-06-01');
INSERT INTO
    Books (book_id, name, available_from)
VALUES
    ('5', 'The Hunger Games', '2008-09-21');
TRUNCATE TABLE Orders;
INSERT INTO
    Orders (order_id, book_id, quantity, dispatch_date)
VALUES
    ('1', '1', '2', '2018-07-26');
INSERT INTO
    Orders (order_id, book_id, quantity, dispatch_date)
VALUES
    ('2', '1', '1', '2018-11-05');
INSERT INTO
    Orders (order_id, book_id, quantity, dispatch_date)
VALUES
    ('3', '3', '8', '2019-06-11');
INSERT INTO
    Orders (order_id, book_id, quantity, dispatch_date)
VALUES
    ('4', '4', '6', '2019-06-05');
INSERT INTO
    Orders (order_id, book_id, quantity, dispatch_date)
VALUES
    ('5', '4', '5', '2019-06-20');
INSERT INTO
    Orders (order_id, book_id, quantity, dispatch_date)
VALUES
    ('6', '5', '9', '2009-02-02');
INSERT INTO
    Orders (order_id, book_id, quantity, dispatch_date)
VALUES
    ('7', '5', '8', '2010-04-13');
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
WITH
    cte AS (
        SELECT
            book_id,
            `name`,
            -- Last 365 days from today
            if (
                dispatch_date BETWEEN '2019-06-23' - INTERVAL 1 YEAR AND '2019-06-23',
                quantity,
                0
            ) AS quanity
            -- Last calendar year
            --if(year(dispatch_date) = year('2019-06-23' - interval 1 year), quantity, 0) as quanity
        FROM
            Books
            LEFT JOIN Orders USING (book_id)
        WHERE
            available_from < '2019-06-23' - INTERVAL 1 MONTH
    )
SELECT
    book_id,
    `name`
FROM
    cte
GROUP BY
    1,
    2
HAVING
    SUM(quanity) < 10;

Duration

1435. Create a Session Bar Chart

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP TABLE if EXISTS Sessions;
CREATE TABLE
    If NOT EXISTS Sessions (session_id INT, duration INT);
TRUNCATE TABLE Sessions;
INSERT INTO
    Sessions (session_id, duration)
VALUES
    ('1', '30');
INSERT INTO
    Sessions (session_id, duration)
VALUES
    ('2', '199');
INSERT INTO
    Sessions (session_id, duration)
VALUES
    ('3', '299');
INSERT INTO
    Sessions (session_id, duration)
VALUES
    ('4', '580');
INSERT INTO
    Sessions (session_id, duration)
VALUES
    ('5', '1000');
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
WITH
    cte (bin, total) AS (
        SELECT
            '[0-5>',
            SUM(duration / 60 < 5)
        FROM
            Sessions
        UNION
        SELECT
            '[5-10>',
            SUM(
                5 <= duration / 60
                AND duration / 60 < 10
            )
        FROM
            Sessions
        UNION
        SELECT
            '[10-15>',
            SUM(
                10 <= duration / 60
                AND duration / 60 < 15
            )
        FROM
            Sessions
        UNION
        SELECT
            '15 or more',
            SUM(15 <= duration / 60)
        FROM
            Sessions
    )
SELECT
    *
FROM
    cte;

1699. Number of Calls Between Two Persons

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 Calls;
CREATE TABLE
    If NOT EXISTS Calls (from_id INT, to_id INT, duration INT);
TRUNCATE TABLE Calls;
INSERT INTO
    Calls (from_id, to_id, duration)
VALUES
    ('1', '2', '59');
INSERT INTO
    Calls (from_id, to_id, duration)
VALUES
    ('2', '1', '11');
INSERT INTO
    Calls (from_id, to_id, duration)
VALUES
    ('1', '3', '20');
INSERT INTO
    Calls (from_id, to_id, duration)
VALUES
    ('3', '4', '100');
INSERT INTO
    Calls (from_id, to_id, duration)
VALUES
    ('3', '4', '200');
INSERT INTO
    Calls (from_id, to_id, duration)
VALUES
    ('3', '4', '200');
INSERT INTO
    Calls (from_id, to_id, duration)
VALUES
    ('4', '3', '499');
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
WITH
    cte AS (
        SELECT
            from_id AS person1,
            to_id AS person2,
            duration
        FROM
            Calls
        UNION ALL
        SELECT
            to_id AS person1,
            from_id AS person2,
            duration
        FROM
            Calls
    )
SELECT
    person1,
    person2,
    COUNT(*) AS call_count,
    SUM(duration) AS total_duration
FROM
    cte
GROUP BY
    1,
    2
HAVING
    person1 < person2;

Time between

1747. Leetflex Banned Accounts

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 LogInfo;
CREATE TABLE
    If NOT EXISTS LogInfo (account_id INT, ip_address INT, login datetime, logout datetime);
TRUNCATE TABLE LogInfo;
INSERT INTO
    LogInfo (account_id, ip_address, login, logout)
VALUES
    ('1', '1', '2021-02-01 09:00:00', '2021-02-01 09:30:00');
INSERT INTO
    LogInfo (account_id, ip_address, login, logout)
VALUES
    ('1', '2', '2021-02-01 08:00:00', '2021-02-01 11:30:00');
INSERT INTO
    LogInfo (account_id, ip_address, login, logout)
VALUES
    ('2', '6', '2021-02-01 20:30:00', '2021-02-01 22:00:00');
INSERT INTO
    LogInfo (account_id, ip_address, login, logout)
VALUES
    ('2', '7', '2021-02-02 20:30:00', '2021-02-02 22:00:00');
INSERT INTO
    LogInfo (account_id, ip_address, login, logout)
VALUES
    ('3', '9', '2021-02-01 16:00:00', '2021-02-01 16:59:59');
INSERT INTO
    LogInfo (account_id, ip_address, login, logout)
VALUES
    ('3', '13', '2021-02-01 17:00:00', '2021-02-01 17:59:59');
INSERT INTO
    LogInfo (account_id, ip_address, login, logout)
VALUES
    ('4', '10', '2021-02-01 16:00:00', '2021-02-01 17:00:00');
INSERT INTO
    LogInfo (account_id, ip_address, login, logout)
VALUES
    ('4', '11', '2021-02-01 17:00:00', '2021-02-01 17:59:59');
1
2
3
4
5
SELECT DISTINCT l1.account_id
FROM LogInfo l1, LogInfo l2
WHERE l1.account_id = l2.account_id
	AND l1.login BETWEEN l2.login AND l2.logout
	AND l1.ip_address != l2.ip_address;