LeetCode SQL - GROUP BY

GROUP BY

262. Trips and Users

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
DROP TABLE IF EXISTS Trips;
DROP TABLE IF EXISTS Users;
CREATE TABLE
If NOT EXISTS Trips (
Id INT,
Client_Id INT,
Driver_Id INT,
City_Id INT,
Status ENUM ('completed', 'cancelled_by_driver', 'cancelled_by_client'),
Request_at VARCHAR(50)
);
CREATE TABLE
If NOT EXISTS Users (Users_Id INT, Banned VARCHAR(50), Role ENUM ('client', 'driver', 'partner'));
TRUNCATE TABLE Trips;
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('1', '1', '10', '1', 'completed', '2013-10-01');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('3', '3', '12', '6', 'completed', '2013-10-01');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('5', '1', '10', '1', 'completed', '2013-10-02');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('6', '2', '11', '6', 'completed', '2013-10-02');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('7', '3', '12', '6', 'completed', '2013-10-02');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('8', '2', '12', '12', 'completed', '2013-10-03');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('9', '3', '10', '12', 'completed', '2013-10-03');
INSERT INTO
Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)
VALUES
('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
TRUNCATE TABLE Users;
INSERT INTO
Users (Users_Id, Banned, Role)
VALUES
('1', 'No', 'client');
INSERT INTO
Users (Users_Id, Banned, Role)
VALUES
('2', 'Yes', 'client');
INSERT INTO
Users (Users_Id, Banned, Role)
VALUES
('3', 'No', 'client');
INSERT INTO
Users (Users_Id, Banned, Role)
VALUES
('4', 'No', 'client');
INSERT INTO
Users (Users_Id, Banned, Role)
VALUES
('10', 'No', 'driver');
INSERT INTO
Users (Users_Id, Banned, Role)
VALUES
('11', 'No', 'driver');
INSERT INTO
Users (Users_Id, Banned, Role)
VALUES
('12', 'No', 'driver');
INSERT INTO
Users (Users_Id, Banned, Role)
VALUES
('13', 'No', 'driver');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/*Select the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03"

- Trips PK: Id
- Trips FK: Client_Id, Driver_Id
- Users PK: Users_Id

- SOLUTION: GROUP BY; BETWEEN
*/
# Step 1. JOIN two tables, SELECT required Banned and Request_at
SELECT
Request_at AS Day,
ROUND(COUNT(IF(Status != 'Completed', 1, NULL)) / COUNT(*), 2) AS 'Cancellation Rate'
FROM Trips
WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03'
AND Client_Id IN (SELECT Users_Id FROM Users WHERE Banned = 'No')
AND Driver_Id IN (SELECT Users_Id FROM Users WHERE Banned = 'No')
GROUP BY Day;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*Select the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03"

- Trips PK: Id
- Trips FK: Client_Id, Driver_Id
- Users PK: Users_Id

- SOLUTION: GROUP BY; BETWEEN
*/
WITH cte AS
(
SELECT Status, Request_at
FROM Trips
INNER JOIN Users u1 ON client_id = u1.users_id
INNER JOIN Users u2 ON driver_id = u2.users_id
WHERE request_at between '2013-10-01' AND '2013-10-03'
AND u1.banned = 'No'
AND u2.banned = 'No'
)
SELECT
request_at AS `Day`,
ROUND(SUM(IF(status != 'completed', 1, 0)) / COUNT(*), 2) AS `Cancellation Rate`
FROM cte
GROUP BY request_at;

614. Second Degree Follower

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 Follow;
CREATE TABLE
If NOT EXISTS Follow (followee VARCHAR(255), follower VARCHAR(255));
TRUNCATE TABLE Follow;
INSERT INTO
Follow (followee, follower)
VALUES
("Caroline", "Robin");
INSERT INTO
Follow (followee, follower)
VALUES
("Robin", "Kylo");
INSERT INTO
Follow (followee, follower)
VALUES
("Caroline", "Kylo");
INSERT INTO
Follow (followee, follower)
VALUES
("Caroline", "Jalen");
INSERT INTO
Follow (followee, follower)
VALUES
("Robin", "Jalen");
INSERT INTO
Follow (followee, follower)
VALUES
("Jalen", "Kylo");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
followee AS follower,
COUNT(followee) AS num
FROM
Follow
WHERE
followee IN (
SELECT
follower
FROM
Follow
)
GROUP BY
1
ORDER BY
1;

1075. Project Employees I

1
2
3
4
5
6
7
8
9
10
/*Query the average experience years of all the employees for each project, rounded 2 digits
- Project PK: (project_id, employee_id)
- Employee PK: employee_id
*/
SELECT
p.project_id,
ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project p
INNER JOIN Employee e USING (employee_id)
GROUP BY p.project_id;

1211. Queries Quality and Percentage

1
2
3
4
5
6
7
8
9
10
11
-- O(nlogn), O(n)
SELECT
query_name,
ROUND(AVG(rating / position), 2) AS quality,
ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage
FROM
Queries
WHERE
query_name IS NOT NULL
GROUP BY
query_name

Reformat

1179. Reformat Department Table

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 Department;
CREATE TABLE
If NOT EXISTS Department (id INT, revenue INT, MONTH VARCHAR(5));
TRUNCATE TABLE Department;
INSERT INTO
Department (id, revenue, MONTH)
VALUES
('1', '8000', 'Jan');
INSERT INTO
Department (id, revenue, MONTH)
VALUES
('2', '9000', 'Jan');
INSERT INTO
Department (id, revenue, MONTH)
VALUES
('3', '10000', 'Feb');
INSERT INTO
Department (id, revenue, MONTH)
VALUES
('1', '7000', 'Feb');
INSERT INTO
Department (id, revenue, MONTH)
VALUES
('1', '6000', 'Mar');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*Reformat the Department table
- Department PK: (id, month)

- SOLUTION: GROUP BY
*/
SELECT
id,
SUM(if (MONTH = 'Jan', revenue, NULL)) AS 'Jan_Revenue',
SUM(if (MONTH = 'Feb', revenue, NULL)) AS 'Feb_Revenue',
SUM(if (MONTH = 'Mar', revenue, NULL)) AS 'Mar_Revenue',
SUM(if (MONTH = 'Apr', revenue, NULL)) AS 'Apr_Revenue',
SUM(if (MONTH = 'May', revenue, NULL)) AS 'May_Revenue',
SUM(if (MONTH = 'Jun', revenue, NULL)) AS 'Jun_Revenue',
SUM(if (MONTH = 'Jul', revenue, NULL)) AS 'Jul_Revenue',
SUM(if (MONTH = 'Aug', revenue, NULL)) AS 'Aug_Revenue',
SUM(if (MONTH = 'Sep', revenue, NULL)) AS 'Sep_Revenue',
SUM(if (MONTH = 'Oct', revenue, NULL)) AS 'Oct_Revenue',
SUM(if (MONTH = 'Nov', revenue, NULL)) AS 'Nov_Revenue',
SUM(if (MONTH = 'Dec', revenue, NULL)) AS 'Dec_Revenue'
FROM
Department
GROUP BY
id;

Dynamic pivot table (not available on LeetCode):

1
2
3
4
5
6
7
8
9
10
select @sql = null;
select group_concat(distinct concat("sum(if(month = '", month, "', revenue, null)) as ", month, "_Revenue")) from Department
into @sql;

select concat("select id, ", @sql, " from Department group by 1")
into @sql;

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

570. Managers with at Least 5 Direct Reports

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),
department varchar(255),
managerId int
);
TRUNCATE TABLE Employee;
INSERT INTO Employee (id, name, department, managerId)
VALUES ('101', 'John', 'A', NULL);
INSERT INTO Employee (id, name, department, managerId)
VALUES ('102', 'Dan', 'A', '101');
INSERT INTO Employee (id, name, department, managerId)
VALUES ('103', 'James', 'A', '101');
INSERT INTO Employee (id, name, department, managerId)
VALUES ('104', 'Amy', 'A', '101');
INSERT INTO Employee (id, name, department, managerId)
VALUES ('105', 'Anne', 'A', '101');
INSERT INTO Employee (id, name, department, managerId)
VALUES ('106', 'Ron', 'B', '101');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*Write an SQL query to report the managers with at least five direct reports.
- Employee PK: (id)
*/
WITH
cte AS (
SELECT
managerId
FROM
Employee
GROUP BY
managerId
HAVING
COUNT(managerId) >= 5
)
SELECT
name
FROM
Employee
INNER JOIN cte ON cte.managerId = Employee.id

1511. Customer Order Frequency

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
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Orders;
CREATE TABLE
If NOT EXISTS Customers (customer_id INT, name VARCHAR(30), country VARCHAR(30));
CREATE TABLE
If NOT EXISTS Product (product_id INT, description VARCHAR(30), price INT);
CREATE TABLE
If NOT EXISTS Orders (order_id INT, customer_id INT, product_id INT, order_date DATE, quantity INT);
TRUNCATE TABLE Customers;
INSERT INTO
Customers (customer_id, name, country)
VALUES
('1', 'Winston', 'USA');
INSERT INTO
Customers (customer_id, name, country)
VALUES
('2', 'Jonathan', 'Peru');
INSERT INTO
Customers (customer_id, name, country)
VALUES
('3', 'Moustafa', 'Egypt');
TRUNCATE TABLE Product;
INSERT INTO
Product (product_id, description, price)
VALUES
('10', 'LC Phone', '300');
INSERT INTO
Product (product_id, description, price)
VALUES
('20', 'LC T-Shirt', '10');
INSERT INTO
Product (product_id, description, price)
VALUES
('30', 'LC Book', '45');
INSERT INTO
Product (product_id, description, price)
VALUES
('40', 'LC Keychain', '2');
TRUNCATE TABLE Orders;
INSERT INTO
Orders (order_id, customer_id, product_id, order_date, quantity)
VALUES
('1', '1', '10', '2020-06-10', '1');
INSERT INTO
Orders (order_id, customer_id, product_id, order_date, quantity)
VALUES
('2', '1', '20', '2020-07-01', '1');
INSERT INTO
Orders (order_id, customer_id, product_id, order_date, quantity)
VALUES
('3', '1', '30', '2020-07-08', '2');
INSERT INTO
Orders (order_id, customer_id, product_id, order_date, quantity)
VALUES
('4', '2', '10', '2020-06-15', '2');
INSERT INTO
Orders (order_id, customer_id, product_id, order_date, quantity)
VALUES
('5', '2', '40', '2020-07-01', '10');
INSERT INTO
Orders (order_id, customer_id, product_id, order_date, quantity)
VALUES
('6', '3', '20', '2020-06-24', '2');
INSERT INTO
Orders (order_id, customer_id, product_id, order_date, quantity)
VALUES
('7', '3', '30', '2020-06-25', '2');
INSERT INTO
Orders (order_id, customer_id, product_id, order_date, quantity)
VALUES
('9', '3', '30', '2020-05-08', '3');
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
/*Select the the customers who spent at least $100 in each month of June and July 2020.
- Customers PK: customer_id
- Product PK: product_id
- Orders PK: order_id

- SOLUTION: JOIN, GROUP BY
*/
WITH
cte AS (
SELECT DISTINCT
(o.customer_id) AS customer_id,
c.name,
date_format (order_date, '%Y-%m') AS DATE,
SUM(quantity * price) AS total
FROM
Orders o
LEFT JOIN Product p USING (product_id)
LEFT JOIN Customers c USING (customer_id)
WHERE
date_format (order_date, '%Y-%m') BETWEEN '2020-06' AND '2020-07'
GROUP BY
1,
2,
3
)
SELECT
customer_id,
`name`
FROM
cte
WHERE
total >= 100
GROUP BY
1,
2
HAVING
COUNT(*) = 2;

GROUP_CONCAT

1484. Group Sold Products By The 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
DROP TABLE if EXISTS Activities;
CREATE TABLE
If NOT EXISTS Activities (sell_date DATE, product VARCHAR(20));
TRUNCATE TABLE Activities;
INSERT INTO
Activities (sell_date, product)
VALUES
('2020-05-30', 'Headphone');
INSERT INTO
Activities (sell_date, product)
VALUES
('2020-06-01', 'Pencil');
INSERT INTO
Activities (sell_date, product)
VALUES
('2020-06-02', 'Mask');
INSERT INTO
Activities (sell_date, product)
VALUES
('2020-05-30', 'Basketball');
INSERT INTO
Activities (sell_date, product)
VALUES
('2020-06-01', 'Bible');
INSERT INTO
Activities (sell_date, product)
VALUES
('2020-06-02', 'Mask');
INSERT INTO
Activities (sell_date, product)
VALUES
('2020-05-30', 'T-Shirt');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
sell_date,
COUNT(DISTINCT product) AS num_sold,
GROUP_CONCAT(
DISTINCT product
ORDER BY
product asc separator ','
) AS products
FROM
Activities
GROUP BY
sell_date
ORDER BY
sell_date