LeetCode SQL - JOIN

Posted on Edited on

LEFT JOIN

175. Combine Two Tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS Address;
CREATE TABLE
    Person (PersonId INT, FirstName VARCHAR(255), LastName VARCHAR(255));
CREATE TABLE
    Address (AddressId INT, PersonId INT, City VARCHAR(255), State VARCHAR(255));
TRUNCATE TABLE Person;
INSERT INTO
    Person (PersonId, LastName, FirstName)
VALUES
    ('1', 'Wang', 'Allen');
TRUNCATE TABLE Address;
INSERT INTO
    Address (AddressId, PersonId, City, State)
VALUES
    ('1', '2', 'New York City', 'New York');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*Select each person in Person table, regradless if there is an address for each of those people
- Person PK: PersonId
- Address PK: AddressId

- SOLUTION: LEFT JOIN
 */
SELECT
    FirstName,
    LastName,
    City,
    State
FROM
    Person
LEFT JOIN Address USING (PersonId);

1988. Find Cutoff Score for Each School

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 Schools;
DROP TABLE if EXISTS Exam;
CREATE TABLE
    If NOT EXISTS Schools (school_id INT, capacity INT);
CREATE TABLE
    If NOT EXISTS Exam (score INT, student_count INT);
TRUNCATE TABLE Schools;
INSERT INTO
    Schools (school_id, capacity)
VALUES
    ('11', '151');
INSERT INTO
    Schools (school_id, capacity)
VALUES
    ('5', '48');
INSERT INTO
    Schools (school_id, capacity)
VALUES
    ('9', '9');
INSERT INTO
    Schools (school_id, capacity)
VALUES
    ('10', '99');
TRUNCATE TABLE Exam;
INSERT INTO
    Exam (score, student_count)
VALUES
    ('975', '10');
INSERT INTO
    Exam (score, student_count)
VALUES
    ('966', '60');
INSERT INTO
    Exam (score, student_count)
VALUES
    ('844', '76');
INSERT INTO
    Exam (score, student_count)
VALUES
    ('749', '76');
INSERT INTO
    Exam (score, student_count)
VALUES
    ('744', '100');
1
2
3
4
5
6
7
8
SELECT
    school_id,
    ifnull (MIN(score), -1) AS score
FROM
    Schools
LEFT JOIN Exam ON capacity >= student_count
GROUP BY
    1;

577. Employee Bonus

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*Write an SQL query to report the name and bonus amount of each employee with a bonus less than 1000.
- Employee PK: empId
- Bonus PK: empId
- Bonus: FK: empId <- Employee
 */
SELECT
    e.name,
    b.bonus
FROM
    Employee e
LEFT JOIN Bonus b USING (empId)
WHERE
    b.bonus < 1000
    OR b.bonus IS NULL;

SELF JOIN

181. Employees Earning More Than Their Managers

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE IF EXISTS Employee;
CREATE TABLE
    If NOT EXISTS Employee (Id INT, Name VARCHAR(255), Salary INT, ManagerId INT);
TRUNCATE TABLE Employee;
INSERT INTO
    Employee (Id, Name, Salary, ManagerId)
VALUES
    ('1', 'Joe', '70000', '3');
INSERT INTO
    Employee (Id, Name, Salary, ManagerId)
VALUES
    ('2', 'Henry', '80000', '4');
INSERT INTO
    Employee (Id, Name, Salary, ManagerId)
VALUES
    ('3', 'Sam', '60000', NULL);
INSERT INTO
    Employee (Id, Name, Salary, ManagerId)
VALUES
    ('4', 'Max', '90000', NULL);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*Select the employees that earning more than their managers
- Employee PK: Id

- Employee: ManagerId could be NULL

- SOLUTION: self join
 */
SELECT
    e1.name AS Employee
FROM
    Employee e1
    INNER JOIN Employee e2 ON e1.managerId = e2.id
WHERE
    e1.salary > e2.salary;

INNER JOIN

197. Rising Temperature

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE IF EXISTS Weather;
CREATE TABLE
    If NOT EXISTS Weather (Id INT, RecordDate DATE, Temperature INT);
TRUNCATE TABLE Weather;
INSERT INTO
    Weather (Id, RecordDate, Temperature)
VALUES
    ('1', '2015-01-01', '10');
INSERT INTO
    Weather (Id, RecordDate, Temperature)
VALUES
    ('2', '2015-01-02', '25');
INSERT INTO
    Weather (Id, RecordDate, Temperature)
VALUES
    ('3', '2015-01-03', '20');
INSERT INTO
    Weather (Id, RecordDate, Temperature)
VALUES
    ('4', '2015-01-04', '30');
1
2
3
4
5
6
7
8
9
10
11
12
/*Campare the temperature with yesterday and select if today is higher.
- Weather PK: id

- SOLUTION: JOIN
 */
SELECT
    w2.id
FROM
    Weather w1
    INNER JOIN Weather w2 ON datediff (w2.recordDate, w1.recordDate) = 1
WHERE
    w2.temperature > w1.temperature;

1501. Countries You Can Safely Invest In

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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
DROP TABLE if EXISTS Person;
DROP TABLE if EXISTS Country;
DROP TABLE if EXISTS Calls;
CREATE TABLE
    If NOT EXISTS Person (id INT, name VARCHAR(15), phone_number VARCHAR(11));
CREATE TABLE
    If NOT EXISTS Country (name VARCHAR(15), country_code VARCHAR(3));
CREATE TABLE
    If NOT EXISTS Calls (caller_id INT, callee_id INT, duration INT);
TRUNCATE TABLE Person;
INSERT INTO
    Person (id, name, phone_number)
VALUES
    ('3', 'Jonathan', '051-1234567');
INSERT INTO
    Person (id, name, phone_number)
VALUES
    ('12', 'Elvis', '051-7654321');
INSERT INTO
    Person (id, name, phone_number)
VALUES
    ('1', 'Moncef', '212-1234567');
INSERT INTO
    Person (id, name, phone_number)
VALUES
    ('2', 'Maroua', '212-6523651');
INSERT INTO
    Person (id, name, phone_number)
VALUES
    ('7', 'Meir', '972-1234567');
INSERT INTO
    Person (id, name, phone_number)
VALUES
    ('9', 'Rachel', '972-0011100');
TRUNCATE TABLE Country;
INSERT INTO
    Country (name, country_code)
VALUES
    ('Peru', '051');
INSERT INTO
    Country (name, country_code)
VALUES
    ('Israel', '972');
INSERT INTO
    Country (name, country_code)
VALUES
    ('Morocco', '212');
INSERT INTO
    Country (name, country_code)
VALUES
    ('Germany', '049');
INSERT INTO
    Country (name, country_code)
VALUES
    ('Ethiopia', '251');
TRUNCATE TABLE Calls;
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('1', '9', '33');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('2', '9', '4');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('1', '2', '59');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('3', '12', '102');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('3', '12', '330');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('12', '3', '5');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('7', '9', '13');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('7', '1', '3');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('9', '7', '1');
INSERT INTO
    Calls (caller_id, callee_id, duration)
VALUES
    ('1', '7', '7');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    co.name AS country
FROM
    person p
INNER JOIN country co ON SUBSTRING(phone_number, 1, 3) = country_code
INNER JOIN calls c ON p.id IN (c.caller_id, c.callee_id)
GROUP BY
    co.name
HAVING
    AVG(duration) > (
        SELECT
            AVG(duration)
        FROM
            calls
    );

2020. Number of Accounts That Did Not Stream

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 Subscriptions;
DROP TABLE if EXISTS Streams;
CREATE TABLE
    If NOT EXISTS Subscriptions (account_id INT, start_date DATE, end_date DATE);
CREATE TABLE
    If NOT EXISTS Streams (session_id INT, account_id INT, stream_date DATE);
TRUNCATE TABLE Subscriptions;
INSERT INTO
    Subscriptions (account_id, start_date, end_date)
VALUES
    ('9', '2020-02-18', '2021-10-30');
INSERT INTO
    Subscriptions (account_id, start_date, end_date)
VALUES
    ('3', '2021-09-21', '2021-11-13');
INSERT INTO
    Subscriptions (account_id, start_date, end_date)
VALUES
    ('11', '2020-02-28', '2020-08-18');
INSERT INTO
    Subscriptions (account_id, start_date, end_date)
VALUES
    ('13', '2021-04-20', '2021-09-22');
INSERT INTO
    Subscriptions (account_id, start_date, end_date)
VALUES
    ('4', '2020-10-26', '2021-05-08');
INSERT INTO
    Subscriptions (account_id, start_date, end_date)
VALUES
    ('5', '2020-09-11', '2021-01-17');
TRUNCATE TABLE Streams;
INSERT INTO
    Streams (session_id, account_id, stream_date)
VALUES
    ('14', '9', '2020-05-16');
INSERT INTO
    Streams (session_id, account_id, stream_date)
VALUES
    ('16', '3', '2021-10-27');
INSERT INTO
    Streams (session_id, account_id, stream_date)
VALUES
    ('18', '11', '2020-04-29');
INSERT INTO
    Streams (session_id, account_id, stream_date)
VALUES
    ('17', '13', '2021-08-08');
INSERT INTO
    Streams (session_id, account_id, stream_date)
VALUES
    ('19', '4', '2020-12-31');
INSERT INTO
    Streams (session_id, account_id, stream_date)
VALUES
    ('13', '5', '2021-01-05');
1
2
3
4
5
6
7
8
SELECT
    COUNT(*) accounts_count
FROM
    Subscriptions s1
INNER JOIN Streams s2 USING (account_id)
WHERE
    YEAR (end_date) = 2021
    AND YEAR (stream_date) != 2021;

1459. Rectangles Area

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DROP TABLE if EXISTS Points;
CREATE TABLE
    If NOT EXISTS Points (id INT, x_value INT, y_value INT);
TRUNCATE TABLE Points;
INSERT INTO
    Points (id, x_value, y_value)
VALUES
    ('1', '2', '7');
INSERT INTO
    Points (id, x_value, y_value)
VALUES
    ('2', '4', '8');
INSERT INTO
    Points (id, x_value, y_value)
VALUES
    ('3', '2', '10');
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    p1.id AS p1,
    p2.id AS p2,
    ABS(p1.x_value - p2.x_value) * ABS(p1.y_value - p2.y_value) AS area
FROM
    Points p1
INNER JOIN Points p2 ON p1.id < p2.id
    AND p1.x_value != p2.x_value
    AND p1.y_value != p2.y_value
ORDER BY
    3 desc,
    1 asc,
    2 asc;

CROSS JOIN

1045. Customers Who Bought All Products

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Product;
CREATE TABLE IF NOT EXISTS Customer (customer_id int, product_key int);
CREATE TABLE Product (product_key int);
TRUNCATE TABLE Customer;
INSERT INTO Customer (customer_id, product_key)
VALUES ('1', '5');
INSERT INTO Customer (customer_id, product_key)
VALUES ('2', '6');
INSERT INTO Customer (customer_id, product_key)
VALUES ('3', '5');
INSERT INTO Customer (customer_id, product_key)
VALUES ('3', '6');
INSERT INTO Customer (customer_id, product_key)
VALUES ('1', '6');
TRUNCATE TABLE Product;
INSERT INTO Product (product_key)
VALUES ('5');
INSERT INTO Product (product_key)
VALUES ('6');
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    customer_id
FROM
    Customer
GROUP BY
    customer_id
HAVING
    COUNT(DISTINCT product_key) = (
        SELECT
            COUNT(product_key)
        FROM
            Product
    );

COUNT

1
2
3
4
5
6
7
8
9
-- O(nlogn), O(n)
SELECT
    customer_id
FROM
    Customer
GROUP BY
    customer_id
HAVING
    COUNT(DISTINCT product_key) = (SELECT COUNT(product_key) FROM Product)

CROSS JOIN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH
    cte AS (
        SELECT DISTINCT
            c.customer_id,
            p.product_key
        FROM
            Customer c
            CROSS JOIN Product p
    )
SELECT
    customer_id
FROM
    cte
    JOIN Customer c USING (customer_id, product_key)
GROUP BY
    1
HAVING
    COUNT(DISTINCT c.product_key) = (SELECT COUNT(product_key) FROM Product)

1633. Percentage of Users Attended a Contest

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
DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (user_id INT, user_name VARCHAR(20));
CREATE TABLE IF NOT EXISTS Register (contest_id INT, user_id INT);
TRUNCATE TABLE Users;
INSERT INTO Users (user_id, user_name)
VALUES ('6', 'Alice');
INSERT INTO Users (user_id, user_name)
VALUES ('2', 'Bob');
INSERT INTO Users (user_id, user_name)
VALUES ('7', 'Alex');
TRUNCATE TABLE Register;
INSERT INTO Register (contest_id, user_id)
VALUES ('215', '6');
INSERT INTO Register (contest_id, user_id)
VALUES ('209', '2');
INSERT INTO Register (contest_id, user_id)
VALUES ('208', '2');
INSERT INTO Register (contest_id, user_id)
VALUES ('210', '6');
INSERT INTO Register (contest_id, user_id)
VALUES ('208', '6');
INSERT INTO Register (contest_id, user_id)
VALUES ('209', '7');
INSERT INTO Register (contest_id, user_id)
VALUES ('209', '6');
INSERT INTO Register (contest_id, user_id)
VALUES ('215', '7');
INSERT INTO Register (contest_id, user_id)
VALUES ('208', '7');
INSERT INTO Register (contest_id, user_id)
VALUES ('210', '2');
INSERT INTO Register (contest_id, user_id)
VALUES ('207', '2');
INSERT INTO Register (contest_id, user_id)
VALUES ('210', '7');

This question has a given dominator.
Therefore, we can use the easy way.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT contest_id,
    round(
        COUNT(user_id) / (
            SELECT COUNT(*)
            FROM Users
        ) * 100,
        2
    ) AS percentage
FROM Register
GROUP BY 1
ORDER BY 2 DESC,
    1 ASC;

If we don’t know the dominator, or the dominator is not the same for each case, we need to use CROSS JOIN.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH cte AS (
    -- Make the table with all the combinations
    SELECT DISTINCT contest_id,
        u.user_id
    FROM Register r
        CROSS JOIN Users u
    ORDER BY 1 ASC
)
SELECT cte.contest_id,
    round(
        COUNT(r.user_id) / COUNT(cte.contest_id) * 100,
        2
    ) AS percentage
FROM cte
    LEFT JOIN Register r ON cte.contest_id = r.contest_id
    AND cte.user_id = r.user_id
GROUP BY 1
ORDER BY 2 DESC,
    1 ASC;