LeetCode SQL - JOIN

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
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH cte AS (
SELECT DISTINCT
customer_id,
p.product_key
FROM Customer c
CROSS JOIN Product p
)
-- SELECT
-- cte.customer_id,
-- c.product_key
-- FROM cte
-- LEFT JOIN Customer c USING(customer_id, product_key);
SELECT cte.customer_id
FROM cte
LEFT JOIN Customer c USING(customer_id, product_key)
GROUP BY 1
HAVING COUNT(DISTINCT c.product_key) = (SELECT COUNT(*)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;