LeetCode SQL - Subquery

Posted on Symbols count in article: 4.7k Reading time ≈ 4 mins.

Subquery

597. Friend Requests I: Overall Acceptance Rate

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
DROP TABLE IF EXISTS FriendRequest;
DROP TABLE IF EXISTS RequestAccepted;
CREATE TABLE
    If NOT EXISTS FriendRequest (sender_id INT, send_to_id INT, request_date DATE);
CREATE TABLE
    If NOT EXISTS RequestAccepted (requester_id INT, accepter_id INT, accept_date DATE);
TRUNCATE TABLE FriendRequest;
INSERT INTO
    FriendRequest (sender_id, send_to_id, request_date)
VALUES
    ('1', '2', '2016/06/01');
INSERT INTO
    FriendRequest (sender_id, send_to_id, request_date)
VALUES
    ('1', '3', '2016/06/01');
INSERT INTO
    FriendRequest (sender_id, send_to_id, request_date)
VALUES
    ('1', '4', '2016/06/01');
INSERT INTO
    FriendRequest (sender_id, send_to_id, request_date)
VALUES
    ('2', '3', '2016/06/02');
INSERT INTO
    FriendRequest (sender_id, send_to_id, request_date)
VALUES
    ('3', '4', '2016/06/09');
TRUNCATE TABLE RequestAccepted;
INSERT INTO
    RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
    ('1', '2', '2016/06/03');
INSERT INTO
    RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
    ('1', '3', '2016/06/08');
INSERT INTO
    RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
    ('2', '3', '2016/06/08');
INSERT INTO
    RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
    ('3', '4', '2016/06/09');
INSERT INTO
    RequestAccepted (requester_id, accepter_id, accept_date)
VALUES
    ('3', '4', '2016/06/10');
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
/*Select overall acceptance rate

- FriendRequest: No PK
- RequestAccepted: No PK

- SOLUTION: Subquery
 */
SELECT
    IFNULL (
        ROUND(
            (
                SELECT
                    COUNT(DISTINCT requester_id, accepter_id)
                FROM
                    RequestAccepted
            ) / (
                SELECT
                    COUNT(DISTINCT sender_id, send_to_id)
                FROM
                    FriendRequest
            ),
            2
        ),
        0
    ) AS accept_rate;

1398. Customers Who Bought Products A and B but Not C

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
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Orders;
CREATE TABLE
    If NOT EXISTS Customers (customer_id INT, customer_name VARCHAR(30));
CREATE TABLE
    If NOT EXISTS Orders (order_id INT, customer_id INT, product_name VARCHAR(30));
TRUNCATE TABLE Customers;
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('1', 'Daniel');
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('2', 'Diana');
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('3', 'Elizabeth');
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('4', 'Jhon');
TRUNCATE TABLE Orders;
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('10', '1', 'A');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('20', '1', 'B');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('30', '1', 'D');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('40', '1', 'C');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('50', '2', 'A');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('60', '3', 'A');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('70', '3', 'B');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('80', '3', 'D');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('90', '4', 'C');
1
2
3
4
5
6
7
8
9
10
11
/*Customers Who Bought Products A and B but Not C
- Customers PK: customer_id
- Orders PK: order_id

- SOLUTION: subquery
*/
SELECT customer_id, customer_name
FROM Customers
WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_name = 'A')
  AND customer_id IN (SELECT customer_id FROM Orders WHERE product_name = 'B')
    AND customer_id NOT IN (SELECT customer_id FROM Orders WHERE product_name = 'C');

[608. Tree Node](https://leetcode.com/problems/t ree-node/)

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 Tree;
CREATE TABLE
    If NOT EXISTS Tree (id INT, p_id INT);
TRUNCATE TABLE Tree;
INSERT INTO
    Tree (id, p_id)
VALUES
    ('1', NULL);
INSERT INTO
    Tree (id, p_id)
VALUES
    ('2', '1');
INSERT INTO
    Tree (id, p_id)
VALUES
    ('3', '1');
INSERT INTO
    Tree (id, p_id)
VALUES
    ('4', '2');
INSERT INTO
    Tree (id, p_id)
VALUES
    ('5', '2');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    id,
    CASE
        WHEN p_id IS NULL THEN 'Root'
        WHEN id IN (
            SELECT
                p_id
            FROM
                Tree
        ) THEN 'Inner'
        ELSE 'Leaf'
    END AS 'type'
FROM
    Tree;

183. Customers Who Never Order

1
2
3
4
5
6
7
8
9
10
11
SELECT
    name AS Customers
FROM
    Customers c
WHERE
    c.id NOT IN (
        SELECT DISTINCT
            customerId
        FROM
            Orders
    );