MySQL LeetCode

Posted on Edited on Symbols count in article: 49k Reading time ≈ 44 mins.

This post contains the universal solutions for each category of Database question on LeetCode.

Ranking

The universal solution for Ranking is CTE + Window Function.

178. Rank Scores

MySQL
1
2
3
4
5
6
7
8
9
DROP TABLE IF EXISTS Scores;
Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1', '3.5');
insert into Scores (Id, Score) values ('2', '3.65');
insert into Scores (Id, Score) values ('3', '4.0');
insert into Scores (Id, Score) values ('4', '3.85');
insert into Scores (Id, Score) values ('5', '4.0');
insert into Scores (Id, Score) values ('6', '3.65');
MySQL
1
2
3
4
5
6
7
8
WITH CTE AS
  (
    SELECT
    Score,
        DENSE_RANK() OVER(ORDER BY score DESC) AS 'Rank'
  FROM Scores
    )
SELECT * FROM CTE;

176. Second Highest Salary

MySQL
1
2
3
4
5
6
7
DROP TABLE IF EXISTS Employee;
Create table If Not Exists Employee (Id int, Salary int);
Truncate table Employee;
insert into Employee (Id, Salary) values ('1', '100');
-- insert into Employee (Id, Salary) values ('2', '100');
insert into Employee (Id, Salary) values ('2', '200');
insert into Employee (Id, Salary) values ('3', '300');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
# DENSE_RANK() the Salary at first
WITH CTE AS
    (
    SELECT
        Salary,
        DENSE_RANK() OVER(ORDER BY Salary DESC) AS Salary_Rank # Descening
    FROM
        Employee
    )
SELECT MAX(Salary) AS SecondHighestSalary # Use MAX() to generate NULL if no record
FROM CTE
WHERE Salary_Rank = 2;

177. Nth Highest Salary

MySQL
1
2
3
4
5
6
7
DROP TABLE IF EXISTS Employee;
Create table If Not Exists Employee (Id int, Salary int);
Truncate table Employee;
insert into Employee (Id, Salary) values ('1', '100');
-- insert into Employee (Id, Salary) values ('2', '100');
insert into Employee (Id, Salary) values ('2', '200');
insert into Employee (Id, Salary) values ('3', '300');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN
    (
    WITH CTE AS
    (
        SELECT
      Salary,
            DENSE_RANK() OVER(ORDER BY Salary DESC) AS Salary_Rank
    FROM Employee
        )
  SELECT MAX(Salary)
    FROM CTE
    WHERE Salary_Rank = N
    );
END

184. Department Highest Salary

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE IF Exists Employee;
DROP TABLE IF Exists Department;
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Jim', '90000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Max', '90000', '1');
Truncate table Department;
insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH CTE AS
  (
  SELECT
    d.Name AS Department,
    e.Name AS Employee,
    e.Salary,
        DENSE_RANK() OVER(PARTITION BY d.Name ORDER BY e.Salary DESC) AS Salary_Rank
  FROM Employee e
  INNER JOIN Department d ON e.DepartmentId = d.Id
    )
SELECT Department, Employee, Salary
FROM CTE
WHERE Salary_Rank = 1;

185. Department Top Three Salaries

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Drop table IF Exists Employee;
Drop table IF Exists Department;
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '85000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Max', '90000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Janet', '69000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('6', 'Randy', '85000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('7', 'Will', '70000', '1');
Truncate table Department;
insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH CTE AS
  (
    SELECT
    d.Name AS Department,
        e.Name AS Employee,
        e.Salary,
        DENSE_RANK() OVER(PARTITION BY d.Name ORDER BY Salary DESC) AS Salary_Rank
  FROM Employee e
    LEFT JOIN Department d ON e.DepartmentId = d.Id
    )
SELECT Department, Employee, Salary
FROM CTE
WHERE Salary_Rank <= 3;

511. Game Play Analysis I

MySQL
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS Activity;
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-05-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH CTE AS
  (
    SELECT
    player_id,
        event_date,
        DENSE_RANK() OVER(PARTITION BY player_id ORDER BY event_date) AS event_date_rank
  FROM Activity
    )
SELECT
  player_id,
    event_date AS first_login
FROM CTE
WHERE event_date_rank = 1;

512. Game Play Analysis II

MySQL
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS Activity;
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-05-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
WITH CTE AS
  (
    SELECT
    player_id,
        device_id,
        event_date,
        DENSE_RANK() OVER(PARTITION BY player_id ORDER BY event_date) AS event_date_rank
  FROM Activity
    )
SELECT player_id, device_id
FROM CTE
WHERE event_date_rank = 1;

1549. The Most Recent Orders for Each Product

MySQL
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 Customers;
DROP table If Exists Orders;
DROP table If Exists Products;
Create table If Not Exists Customers (customer_id int, name varchar(10));
Create table If Not Exists Orders (order_id int, order_date date, customer_id int, product_id int);
Create table If Not Exists Products (product_id int, product_name varchar(20), price int);
Truncate table Customers;
insert into Customers (customer_id, name) values ('1', 'Winston');
insert into Customers (customer_id, name) values ('2', 'Jonathan');
insert into Customers (customer_id, name) values ('3', 'Annabelle');
insert into Customers (customer_id, name) values ('4', 'Marwan');
insert into Customers (customer_id, name) values ('5', 'Khaled');
Truncate table Orders;
insert into Orders (order_id, order_date, customer_id, product_id) values ('1', '2020-07-31', '1', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('2', '2020-7-30', '2', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('3', '2020-08-29', '3', '3');
insert into Orders (order_id, order_date, customer_id, product_id) values ('4', '2020-07-29', '4', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('5', '2020-06-10', '1', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('6', '2020-08-01', '2', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('7', '2020-08-01', '3', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('8', '2020-08-03', '1', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('9', '2020-08-07', '2', '3');
insert into Orders (order_id, order_date, customer_id, product_id) values ('10', '2020-07-15', '1', '2');
Truncate table Products;
insert into Products (product_id, product_name, price) values ('1', 'keyboard', '120');
insert into Products (product_id, product_name, price) values ('2', 'mouse', '80');
insert into Products (product_id, product_name, price) values ('3', 'screen', '600');
insert into Products (product_id, product_name, price) values ('4', 'hard disk', '450');
MySQL
1
2
3
4
5
6
7
8
9
10
11
WITH CTE AS
  (
  SELECT product_name, p.product_id, order_id, customer_id, order_date, 
    DENSE_RANK() OVER (PARTITION BY p.product_id ORDER BY order_date DESC) AS ranking
  FROM Products p
  LEFT JOIN Orders o ON p.product_id = o.product_id
    )
SELECT product_name, product_id, order_id, order_date
FROM CTE
WHERE ranking = 1 AND order_date IS NOT NULL
ORDER BY product_name, product_id, order_id;

1532. The Most Recent Three Orders

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DROP table If Exists Customers;
DROP table If Exists Orders;
Create table If Not Exists Customers (customer_id int, name varchar(10));
Create table If Not Exists Orders (order_id int, order_date date, customer_id int, cost int);
Truncate table Customers;
insert into Customers (customer_id, name) values ('1', 'Winston');
insert into Customers (customer_id, name) values ('2', 'Jonathan');
insert into Customers (customer_id, name) values ('3', 'Annabelle');
insert into Customers (customer_id, name) values ('4', 'Marwan');
insert into Customers (customer_id, name) values ('5', 'Khaled');
Truncate table Orders;
insert into Orders (order_id, order_date, customer_id, cost) values ('1', '2020-07-31', '1', '30');
insert into Orders (order_id, order_date, customer_id, cost) values ('2', '2020-7-30', '2', '40');
insert into Orders (order_id, order_date, customer_id, cost) values ('3', '2020-07-31', '3', '70');
insert into Orders (order_id, order_date, customer_id, cost) values ('4', '2020-07-29', '4', '100');
insert into Orders (order_id, order_date, customer_id, cost) values ('5', '2020-06-10', '1', '1010');
insert into Orders (order_id, order_date, customer_id, cost) values ('6', '2020-08-01', '2', '102');
insert into Orders (order_id, order_date, customer_id, cost) values ('7', '2020-08-01', '3', '111');
insert into Orders (order_id, order_date, customer_id, cost) values ('8', '2020-08-03', '1', '99');
insert into Orders (order_id, order_date, customer_id, cost) values ('9', '2020-08-07', '2', '32');
insert into Orders (order_id, order_date, customer_id, cost) values ('10', '2020-07-15', '1', '2');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH CTE AS
  (
    SELECT
    p.product_name, p.product_id,
        o.order_id, o.order_date,
        DENSE_RANK() OVER(PARTITION BY p.product_id ORDER BY order_date DESC) AS order_date_rank
  FROM Products p
    INNER JOIN Orders o ON p.product_id = o.product_id
    )
SELECT product_name, product_id, order_id, order_date
FROM CTE
WHERE order_date_rank = 1
ORDER BY product_name ASC, order_id ASC;

1596. The Most Frequently Ordered Products for Each Customer

MySQL
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
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;

Create table If Not Exists Customers (customer_id int, name varchar(10));
Create table If Not Exists Orders (order_id int, order_date date, customer_id int, product_id int);
Create table If Not Exists Products (product_id int, product_name varchar(20), price int);
Truncate table Customers;
insert into Customers (customer_id, name) values ('1', 'Alice');
insert into Customers (customer_id, name) values ('2', 'Bob');
insert into Customers (customer_id, name) values ('3', 'Tom');
insert into Customers (customer_id, name) values ('4', 'Jerry');
insert into Customers (customer_id, name) values ('5', 'John');
Truncate table Orders;
insert into Orders (order_id, order_date, customer_id, product_id) values ('1', '2020-07-31', '1', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('2', '2020-7-30', '2', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('3', '2020-08-29', '3', '3');
insert into Orders (order_id, order_date, customer_id, product_id) values ('4', '2020-07-29', '4', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('5', '2020-06-10', '1', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('6', '2020-08-01', '2', '1');
insert into Orders (order_id, order_date, customer_id, product_id) values ('7', '2020-08-01', '3', '3');
insert into Orders (order_id, order_date, customer_id, product_id) values ('8', '2020-08-03', '1', '2');
insert into Orders (order_id, order_date, customer_id, product_id) values ('9', '2020-08-07', '2', '3');
insert into Orders (order_id, order_date, customer_id, product_id) values ('10', '2020-07-15', '1', '2');
Truncate table Products;
insert into Products (product_id, product_name, price) values ('1', 'keyboard', '120');
insert into Products (product_id, product_name, price) values ('2', 'mouse', '80');
insert into Products (product_id, product_name, price) values ('3', 'screen', '600');
insert into Products (product_id, product_name, price) values ('4', 'hard disk', '450');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH CTE AS
  (
    SELECT
    customer_id,
        product_id,
        # COUNT(product_id) # Review the number of orders per customer
        DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY COUNT(product_id) DESC) AS count_orders_rank
  FROM Orders
    GROUP BY customer_id, product_id
    )
# SELECT * FROM CTE;
SELECT
  CTE.customer_id, CTE.product_id,
    p.product_name
FROM CTE
INNER JOIN products p ON CTE.product_id = p.product_id
WHERE count_orders_rank = 1
ORDER BY customer_id;

Window Function

534. Game Play Analysis III

MySQL
1
2
3
4
5
6
7
8
Drop table If Exists Activity;
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-05-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5');
MySQL
1
2
3
4
5
SELECT
  player_id,
    event_date,
    SUM(games_played) OVER(PARTITION BY player_id ORDER BY event_date) AS games_played_so_far
FROM Activity;

1412. Find the Quiet Students in All Exams

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Exam;
Create table If Not Exists Student (student_id int, student_name varchar(30)) ;
Create table If Not Exists Exam (exam_id int, student_id int, score int) ;
Truncate table Student ;
insert into Student (student_id, student_name) values ('1', 'Daniel') ;
insert into Student (student_id, student_name) values ('2', 'Jade') ;
insert into Student (student_id, student_name) values ('3', 'Stella') ;
insert into Student (student_id, student_name) values ('4', 'Jonathan') ;
insert into Student (student_id, student_name) values ('5', 'Will') ;
Truncate table Exam ;
insert into Exam (exam_id, student_id, score) values ('10', '1', '70') ;
insert into Exam (exam_id, student_id, score) values ('10', '2', '80') ;
insert into Exam (exam_id, student_id, score) values ('10', '3', '90') ;
insert into Exam (exam_id, student_id, score) values ('20', '1', '80') ;
insert into Exam (exam_id, student_id, score) values ('30', '1', '70') ;
insert into Exam (exam_id, student_id, score) values ('30', '3', '80') ;
insert into Exam (exam_id, student_id, score) values ('30', '4', '90') ;
insert into Exam (exam_id, student_id, score) values ('40', '1', '60') ;
insert into Exam (exam_id, student_id, score) values ('40', '2', '70') ;
insert into Exam (exam_id, student_id, score) values ('40', '4', '80');
MySQL
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
# SELECT the students who are in the middle of the rank of ALL exams
# Student PK: student_id
# possible issues: dense rank; same value
# Exam PK: (exam_id, student_id)
# Step 1: Dense rank the students with ascending and descending for each exam
WITH CTE AS
  (
  SELECT
    e.exam_id,
    e.student_id ,
    s.student_name,
    e.score,
    DENSE_RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS DR_desc,
        DENSE_RANK() OVER(PARTITION BY exam_id ORDER BY score) AS DR_asc
  FROM exam e
  INNER JOIN Student s
    ON e.student_id = s.student_id
  )
# Step 2: Select the quiet student
SELECT DISTINCT student_id, student_name
FROM CTE
WHERE student_id NOT IN (SELECT student_id FROM CTE WHERE DR_desc = 1 OR DR_asc = 1)
ORDER BY student_id;
````

---

## JOIN

JOIN contains join to other table and self-join.

---

### [175. Combine Two Tables](https://leetcode.com/problems/combine-two-tables/)

```sql MySQL
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');
MySQL
1
2
3
4
5
SELECT
  FirstName, LastName,
    City, State
FROM Person p
LEFT JOIN Address a ON p.PersonId = a.PersonId;

181. Employees Earning More Than Their Managers

MySQL
1
2
3
4
5
6
7
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);
MySQL
1
2
3
4
SELECT e1.Name AS Employee
FROM Employee e1
INNER JOIN Employee e2 ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary;

197. Rising Temperature

MySQL
1
2
3
4
5
6
7
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');
MySQL
1
2
3
4
SELECT w2.id
FROM Weather w1
INNER JOIN Weather w2 ON DATEDIFF(w2.recordDate, w1.recordDate) = 1 # Don't use `w1.id = w2.id - 1` since the record may be shuffled.
WHERE w2.Temperature > w1.Temperature;

613. Shortest Distance in a Line

MySQL
1
2
3
4
5
6
DROP TABLE IF EXISTS point;
CREATE TABLE If Not Exists point (x INT NOT NULL, UNIQUE INDEX x_UNIQUE (x ASC));
Truncate table point;
insert into point (x) values ('-1');
insert into point (x) values ('0');
insert into point (x) values ('2');
MySQL
1
2
3
4
5
6
7
8
9
10
WITH CTE AS
  (
  SELECT 
    p1.x AS x1, 
    p2.x AS x2,
    ABS(p1.x - p2.x) AS distance
  FROM point p1
  LEFT JOIN point p2 ON p1.x != p2.x
    )
SELECT MIN(distance) AS shortest FROM CTE;

TRANSACTIONS

1587. Bank Account Summary II

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Transactions;
Create table If Not Exists Users (account int, name varchar(20));
Create table If Not Exists Transactions (trans_id int, account int, amount int, transacted_on date);
Truncate table Users;
insert into Users (account, name) values ('900001', 'Alice');
insert into Users (account, name) values ('900002', 'Bob');
insert into Users (account, name) values ('900003', 'Charlie');
Truncate table Transactions;
insert into Transactions (trans_id, account, amount, transacted_on) values ('1', '900001', '7000', '2020-08-01');
insert into Transactions (trans_id, account, amount, transacted_on) values ('2', '900001', '7000', '2020-09-01');
insert into Transactions (trans_id, account, amount, transacted_on) values ('3', '900001', '-3000', '2020-09-02');
insert into Transactions (trans_id, account, amount, transacted_on) values ('4', '900002', '1000', '2020-09-12');
insert into Transactions (trans_id, account, amount, transacted_on) values ('5', '900003', '6000', '2020-08-07');
insert into Transactions (trans_id, account, amount, transacted_on) values ('6', '900003', '6000', '2020-09-07');
insert into Transactions (trans_id, account, amount, transacted_on) values ('7', '900003', '-4000', '2020-09-11');
MySQL
1
2
3
4
5
6
7
SELECT
  name,
    SUM(amount) AS balance
FROM Users u
LEFT JOIN Transactions t ON u.account = t.account
GROUP BY name
HAVING balance > 10000;

1555. Bank Account Summary

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Transactions;
Create table If Not Exists Users (user_id int, user_name varchar(20), credit int);
Create table If Not Exists Transactions (trans_id int, paid_by int, paid_to int, amount int, transacted_on date);
Truncate table Users;
insert into Users (user_id, user_name, credit) values ('1', 'Moustafa', '100');
insert into Users (user_id, user_name, credit) values ('2', 'Jonathan', '200');
insert into Users (user_id, user_name, credit) values ('3', 'Winston', '10000');
insert into Users (user_id, user_name, credit) values ('4', 'Luis', '800');
Truncate table Transactions;
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('1', '1', '3', '400', '2020-08-01');
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('2', '3', '2', '500', '2020-08-02');
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('3', '2', '1', '200', '2020-08-03');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Transactions;
Create table If Not Exists Users (user_id int, user_name varchar(20), credit int);
Create table If Not Exists Transactions (trans_id int, paid_by int, paid_to int, amount int, transacted_on date);
Truncate table Users;
insert into Users (user_id, user_name, credit) values (1, "Winston", 100);
insert into Users (user_id, user_name, credit) values (2, "Moustafa", 600);
insert into Users (user_id, user_name, credit) values (3, "Jonathan", 800);
insert into Users (user_id, user_name, credit) values (4, "Maria", 100);
Truncate table Transactions;
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values (1, 2, 4, 1000, "2020-08-28");
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values (2, 3, 2, 600, "2020-08-06");
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values (3, 2, 4, 800, "2020-08-15");
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values (4, 1, 3, 800, "2020-09-02");
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values (5, 3, 4, 100, "2020-08-02");
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values (6, 3, 4, 500, "2020-08-08");
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values (7, 3, 4, 800, "2020-09-17");
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH RECURSIVE
CTE AS
  (
  SELECT paid_by AS user_id, (-amount) AS amount
  FROM Transactions
  UNION ALL
  SELECT paid_to AS user_id, amount AS amount
  FROM Transactions
    UNION ALL
    SELECT user_id, credit
    FROM Users
    ),
# SELECT * FROM CTE;
CTE1 AS
  (
  SELECT
    c.user_id, u.user_name, 
    SUM(amount) AS credit
  FROM CTE c
  INNER JOIN Users u ON c.user_id = u.user_id
  GROUP BY user_id, user_name
  )
SELECT *, IF(credit < 0, 'Yes', 'No') AS credit_limit_breached
FROM CTE1;

1843. Suspicious Bank Accounts

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS Transactions;
Create table If Not Exists Accounts (account_id int, max_income int);
Create table If Not Exists Transactions (transaction_id int, account_id int, type ENUM('creditor', 'debtor'), amount int, day datetime);
Truncate table Accounts;
insert into Accounts (account_id, max_income) values ('3', '21000');
insert into Accounts (account_id, max_income) values ('4', '10400');
Truncate table Transactions;
insert into Transactions (transaction_id, account_id, type, amount, day) values ('2', '3', 'Creditor', '107100', '2021-06-02 11:38:14');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('4', '4', 'Creditor', '10400', '2021-06-20 12:39:18');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('11', '4', 'Debtor', '58800', '2021-07-23 12:41:55');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('1', '4', 'Creditor', '49300', '2021-05-03 16:11:04');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('15', '3', 'Debtor', '75500', '2021-05-23 14:40:20');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('10', '3', 'Creditor', '102100', '2021-06-15 10:37:16');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('14', '4', 'Creditor', '56300', '2021-07-21 12:12:25');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('19', '4', 'Debtor', '101100', '2021-05-09 15:21:49');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('8', '3', 'Creditor', '64900', '2021-07-26 15:09:56');
insert into Transactions (transaction_id, account_id, type, amount, day) values ('7', '3', 'Creditor', '90900', '2021-06-14 11:23:07');
MySQL
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 RECURSIVE
income_per_month AS
  (
  SELECT
    account_id,
    DATE_FORMAT(day, '%Y%m') AS 'date',
    SUM(amount) AS amount
  FROM Transactions
  WHERE type = 'Creditor'
  GROUP BY account_id, date
  ORDER BY account_id, date
  ),
# SELECT * FROM income_per_month;
exceed_table AS
  (
  SELECT
    i.account_id, i.date, i.amount,
        max_income,
        IF(amount > max_income, 1, 0) AS if_exceed
  FROM income_per_month i
  LEFT JOIN Accounts a ON i.account_id = a.account_id
  )
# SELECT * FROM exceed_table;
SELECT DISTINCT(e1.account_id)
FROM exceed_table e1
INNER JOIN exceed_table e2 ON e1.account_id = e2.account_id
  AND e1.date = e2.date - 1
WHERE e1.if_exceed + e2.if_exceed > 1;

1581. Customer Who Visited but Did Not Make Any Transactions

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DROP TABLE IF EXISTS Visits;
DROP TABLE IF EXISTS Transactions;
Create table If Not Exists Visits(visit_id int, customer_id int);
Create table If Not Exists Transactions(transaction_id int, visit_id int, amount int);
Truncate table Visits;
insert into Visits (visit_id, customer_id) values ('1', '23');
insert into Visits (visit_id, customer_id) values ('2', '9');
insert into Visits (visit_id, customer_id) values ('4', '30');
insert into Visits (visit_id, customer_id) values ('5', '54');
insert into Visits (visit_id, customer_id) values ('6', '96');
insert into Visits (visit_id, customer_id) values ('7', '54');
insert into Visits (visit_id, customer_id) values ('8', '54');
Truncate table Transactions;
insert into Transactions (transaction_id, visit_id, amount) values ('2', '5', '310');
insert into Transactions (transaction_id, visit_id, amount) values ('3', '5', '300');
insert into Transactions (transaction_id, visit_id, amount) values ('9', '5', '200');
insert into Transactions (transaction_id, visit_id, amount) values ('12', '1', '910');
insert into Transactions (transaction_id, visit_id, amount) values ('13', '2', '970');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH CTE AS
  (
  SELECT
    v.visit_id, v.customer_id,
    t.transaction_id, t.amount
  FROM Visits v
  LEFT JOIN Transactions t ON v.visit_id = t.visit_id
  ORDER BY customer_id, visit_id
    )
SELECT customer_id, COUNT(customer_id) AS count_no_trans
FROM CTE
WHERE transaction_id IS NULL
GROUP BY customer_id;

1193. Monthly Transactions I

MySQL
1
2
3
4
5
6
7
DROP TABLE IF EXISTS Transactions;
create table if not exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date);
Truncate table Transactions;
insert into Transactions (id, country, state, amount, trans_date) values ('121', 'US', 'approved', '1000', '2018-12-18');
insert into Transactions (id, country, state, amount, trans_date) values ('122', 'US', 'declined', '2000', '2018-12-19');
insert into Transactions (id, country, state, amount, trans_date) values ('123', 'US', 'approved', '2000', '2019-01-01');
insert into Transactions (id, country, state, amount, trans_date) values ('124', 'DE', 'approved', '2000', '2019-01-07');
MySQL
1
2
3
4
5
6
7
8
9
SELECT
  DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(state) AS trans_count,
    SUM(IF(state='approved', 1, 0)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state='approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY month, country;

1205. Monthly Transactions II

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TABLE IF EXISTS Transactions;
DROP TABLE IF EXISTS Chargebacks;
create table if not exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date);
create table if not exists Chargebacks (trans_id int, trans_date date);
Truncate table Transactions;
insert into Transactions (id, country, state, amount, trans_date) values ('101', 'US', 'approved', '1000', '2019-05-18');
insert into Transactions (id, country, state, amount, trans_date) values ('102', 'US', 'declined', '2000', '2019-05-19');
insert into Transactions (id, country, state, amount, trans_date) values ('103', 'US', 'approved', '3000', '2019-06-10');
insert into Transactions (id, country, state, amount, trans_date) values ('104', 'US', 'declined', '4000', '2019-06-13');
insert into Transactions (id, country, state, amount, trans_date) values ('105', 'US', 'approved', '5000', '2019-06-15');
Truncate table Chargebacks;
insert into Chargebacks (trans_id, trans_date) values ('102', '2019-05-29');
insert into Chargebacks (trans_id, trans_date) values ('101', '2019-06-30');
insert into Chargebacks (trans_id, trans_date) values ('105', '2019-09-18');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE IF EXISTS Transactions;
DROP TABLE IF EXISTS Chargebacks;
create table if not exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date);
create table if not exists Chargebacks (trans_id int, trans_date date);
Truncate table Transactions;
insert into Transactions (id, country, state, amount, trans_date) values (100,"CB","declined",4000,"2019-02-04");
insert into Transactions (id, country, state, amount, trans_date) values (101,"BB","approved",7000,"2019-02-17");
insert into Transactions (id, country, state, amount, trans_date) values (102,"CA","declined",6000,"2019-02-26");
insert into Transactions (id, country, state, amount, trans_date) values (103,"AA","declined",7000,"2019-04-01");
Truncate table Chargebacks;
insert into Chargebacks (trans_id, trans_date) values (100,"2019-03-29");
insert into Chargebacks (trans_id, trans_date) values (102,"2019-02-28");
insert into Chargebacks (trans_id, trans_date) values (103,"2019-05-09");

Simplest MySQL Solution with Detailed Explanation

MySQL
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
WITH CTE AS
  (
  SELECT
    id, country, state, amount,
    DATE_FORMAT(trans_date, '%Y-%m') AS month
  FROM Transactions
  WHERE state = 'approved'
  UNION
  SELECT
    c.trans_id, t.country, 'chargeback' AS state, t.amount,
    DATE_FORMAT(c.trans_date, '%Y-%m') AS month
  FROM Chargebacks c
  LEFT JOIN Transactions t ON c.trans_id = t.id
  )
# SELECT * FROM CTE;
SELECT
  month,
    country,
    SUM(IF(state='approved', 1, 0)) AS approved_count,
    SUM(IF(state='approved', amount, 0)) AS approved_amount,
    SUM(IF(state='chargeback', 1, 0)) AS chargeback_count,
    SUM(IF(state='chargeback', amount, 0)) AS chargeback_amount
FROM CTE
GROUP BY month, country
ORDER BY month, country;

GROUPING

196. Delete Duplicate Emails

MySQL
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS Person;
Create Table IF NOT EXISTS Person (Id INT, Email VARCHAR(30));
Truncate table Person;
insert into Person (Id, Email) values ('1', 'john@example.com');
insert into Person (Id, Email) values ('2', 'bob@example.com');
insert into Person (Id, Email) values ('3', 'john@example.com');

SELECT * FROM Person;
MySQL
1
2
3
4
5
6
7
8
WITH CTE AS
  (
  SELECT MIN(Id) AS Id
  FROM Person
  GROUP BY Email
    )
DELETE FROM Person
WHERE Id NOT IN (SELECT Id FROM CTE);

182. Duplicate Emails

MySQL
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS Person;
Create table If Not Exists Person (Id int, Email varchar(255));
Truncate table Person;
insert into Person (Id, Email) values ('1', 'a@b.com');
insert into Person (Id, Email) values ('2', 'c@d.com');
insert into Person (Id, Email) values ('3', 'a@b.com');

SELECT * FROM Person;
MySQL
1
2
3
4
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) >= 2;

596. Classes More Than 5 Students

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE IF EXISTS courses;
Create table If Not Exists courses (student varchar(255), class varchar(255));
Truncate table courses;
insert into courses (student, class) values ('A', 'Math');
insert into courses (student, class) values ('B', 'English');
insert into courses (student, class) values ('C', 'Math');
insert into courses (student, class) values ('D', 'Biology');
insert into courses (student, class) values ('E', 'Math');
insert into courses (student, class) values ('F', 'Computer');
insert into courses (student, class) values ('G', 'Math');
insert into courses (student, class) values ('H', 'Math');
insert into courses (student, class) values ('I', 'Math');
MySQL
1
2
3
4
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT(student)) >= 5;

619. Biggest Single Number

MySQL
1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS my_numbers;
Create table If Not Exists my_numbers (num int);
Truncate table my_numbers;
insert into my_numbers (num) values ('8');
insert into my_numbers (num) values ('8');
insert into my_numbers (num) values ('3');
insert into my_numbers (num) values ('3');
insert into my_numbers (num) values ('1');
insert into my_numbers (num) values ('4');
insert into my_numbers (num) values ('5');
insert into my_numbers (num) values ('6');
MySQL
1
2
3
4
5
6
7
8
WITH CTE AS
  (
  SELECT num
  FROM my_numbers
  GROUP BY num
  HAVING COUNT(num) = 1
    )
SELECT MAX(num) AS num FROM CTE;

262. Trips and Users

MySQL
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 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');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/*
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: User_Id

SOLUTION: GROUP BY; BETWEEN
*/
SELECT Request_at as Day,
       ROUND(COUNT(IF(Status != 'completed', TRUE, 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 Request_at;
MySQL
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
/*
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: User_Id

SOLUTION: JOIN; GROUP BY
*/
# Step 1: Join two tables and mark banned for drivers and clients
WITH CTE AS
  (
  SELECT
    t.Id,
    t.Request_at AS Day,
    t.Client_Id,
    t.Driver_Id,
        t.Status,
    u1.Banned AS Client_Banned,
    u2.Banned AS Driver_Banned
  FROM Trips t
  INNER JOIN Users u1
    ON t.Client_Id = u1.Users_Id
  INNER JOIN Users u2
    ON t.Driver_Id = u2.Users_Id
  )
# Step 2: Select no banned users and the date in the required range
SELECT 
    Day,
    ROUND(1 - SUM(IF(Status = 'completed', 1, 0)) / COUNT(*), 2) AS `Cancellation Rate`
FROM CTE
WHERE Client_Banned = 'No'
  AND Driver_Banned = 'No'
    AND Day BETWEEN "2013-10-01" AND "2013-10-03"
GROUP BY Day
ORDER BY Day;

WITH RECURSIVE

1270. All People Report to the Given Manager

MySQL
1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS Employees;
Create table If Not Exists Employees (employee_id int, employee_name varchar(30), manager_id int);
Truncate table Employees;
insert into Employees (employee_id, employee_name, manager_id) values ('1', 'Boss', '1');
insert into Employees (employee_id, employee_name, manager_id) values ('3', 'Alice', '3');
insert into Employees (employee_id, employee_name, manager_id) values ('2', 'Bob', '1');
insert into Employees (employee_id, employee_name, manager_id) values ('4', 'Daniel', '2');
insert into Employees (employee_id, employee_name, manager_id) values ('7', 'Luis', '4');
insert into Employees (employee_id, employee_name, manager_id) values ('8', 'John', '3');
insert into Employees (employee_id, employee_name, manager_id) values ('9', 'Angela', '8');
insert into Employees (employee_id, employee_name, manager_id) values ('77', 'Robert', '1');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/* Select the employees that report to the head.
SOLUTION: WITH RECURSIVE; UNION ALL
*/
WITH RECURSIVE
CTE(employee_id, manager_id) AS
    (
    # 1st query
    SELECT employee_id, CAST(manager_id AS CHAR(30))
    FROM Employees
    WHERE employee_id = 1
    # the rest queries
    UNION ALL
    SELECT e.employee_id, CONCAT(e.employee_id, '-->', c.manager_id)
    FROM Employees e
    INNER JOIN CTE c
        ON e.manager_id = c.employee_id
    WHERE e.employee_id != 1
    )
#SELECT * FROM CTE;
SELECT employee_id FROM CTE WHERE employee_id != 1;

SWAP

627. Swap Salary

MySQL
1
2
3
4
5
6
7
DROP TABLE IF EXISTS Salary;
create table if not exists Salary(id int, name varchar(100), sex char(1), salary int);
Truncate table Salary;
insert into Salary (id, name, sex, salary) values ('1', 'A', 'm', '2500');
insert into Salary (id, name, sex, salary) values ('2', 'B', 'f', '1500');
insert into Salary (id, name, sex, salary) values ('3', 'C', 'm', '5500');
insert into Salary (id, name, sex, salary) values ('4', 'D', 'f', '500');
MySQL
1
2
UPDATE Salary
SET sex = IF(sex='f', 'm', 'f');

626. Exchange Seats

MySQL
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS seat;
Create table If Not Exists seat(id int, student varchar(255));
Truncate table seat;
insert into seat (id, student) values ('1', 'Abbot');
insert into seat (id, student) values ('2', 'Doris');
insert into seat (id, student) values ('3', 'Emerson');
insert into seat (id, student) values ('4', 'Green');
insert into seat (id, student) values ('5', 'Jeames');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
/* Exchange the records
SOLUTION: CASE
*/
SELECT
    CASE
        WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM seat) THEN id
        WHEN id % 2 = 0 THEN id - 1
        WHEN id % 2 = 1 THEN id + 1
    END AS id,
    student
FROM seat
ORDER BY id;

REFORMAT

1179. Reformat Department Table

MySQL
1
2
3
4
5
6
7
8
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');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;

1777. Product’s Price for Each Store

MySQL
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS Products;
Create table If Not Exists Products (product_id int, store ENUM('store1', 'store2', 'store3'), price int);
Truncate table Products;
insert into Products (product_id, store, price) values ('0', 'store1', '95');
insert into Products (product_id, store, price) values ('0', 'store3', '105');
insert into Products (product_id, store, price) values ('0', 'store2', '100');
insert into Products (product_id, store, price) values ('1', 'store1', '70');
insert into Products (product_id, store, price) values ('1', 'store3', '80');
MySQL
1
2
3
4
5
6
SELECT product_id, 
  SUM(IF(store = 'store1', price, NULL)) AS store1,
  SUM(IF(store = 'store2', price, NULL)) AS store2,
  SUM(IF(store = 'store3', price, NULL)) AS store3
FROM Products
GROUP BY product_id;

Subquery

597. Friend Requests I: Overall Acceptance Rate

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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');
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
/*
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;