SQL LeetCode

LeetCode

---

Ranking

---

176. Second Highest Salary

176. Second Highest Salary

MySQL

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');

Approach: Using sub-query and LIMIT clause [Accepted] 273 ms

• ORDER BY Salary DESC
• The second one
  • LIMIT 1 OFFSET 1

MySQL

SELECT 
    DISTINCT Salary AS SecondHighestSalary
FROM 
    Employee
ORDER BY 
    Salary DESC
LIMIT 
    1 
OFFSET
    1;

However, this solution will be judged as ‘Wrong Answer’ if there is no such second highest salary since there might be only one record in this table. To overcome this issue, we can take this as a temp table.

MySQL

SELECT
    (SELECT
    DISTINCT Salary
  FROM
    Employee
  ORDER BY
    Salary DESC
  LIMIT
    1
  OFFSET
    1) AS SecondHighestSalary;

---

Approach: RANK() and CTE

MySQL

WITH CTE AS
  (
    SELECT
    Salary, DENSE_RANK()
  OVER (ORDER BY Salary DESC) AS ranking
    FROM
    Employee
  )
SELECT
  MAX(Salary)  AS SecondHighestSalary # Using MAX() to generate NULL if no result
FROM
  CTE
WHERE
  ranking = 2
;

---

177. Nth Highest Salary

177. Nth Highest Salary

MySQL

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');

---

Approach: FUNCTION, DENSE_RANK(), and CTE

MySQL

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN
        (
        WITH CTE AS
            (
            SELECT Salary, DENSE_RANK()
                OVER (ORDER BY Salary DESC) AS ranking
            FROM
                Employee
            )
        SELECT
            MAX(Salary)
        FROM
            CTE
        WHERE
            ranking = N
        );
END

---

184. Department Highest Salary

184. Department Highest Salary

MySQL

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');

---

Approach Using JOIN and IN clause [Accepted] 590 ms

MySQL

-- Select hightest salary group by DepartmentId
SELECT 
  DepartmentId, 
  MAX(Salary)
FROM
  Employee
GROUP BY
  DepartmentId;

-- Select Department Highest Salary
SELECT
  d.Name AS Department,
    e.Name AS Employee,
    e.Salary
FROM
  Employee e
JOIN
  Department d ON e.DepartmentId = d.Id
WHERE
  (d.Id, e.Salary) 
    IN
  (SELECT 
    DepartmentId, 
        MAX(Salary)
  FROM
    Employee
  GROUP BY
    DepartmentId);

---

185. Department Top Three Salaries

185. Department Top Three Salaries

Schema

MySQL

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');

---

Approach: CTE and DENSE_RANK()

MySQL

WITH CTE AS
  (
  SELECT
    d.Name AS Department, e.Name AS Employee, e.Salary, DENSE_RANK()
  OVER (PARTITION BY e.DepartmentId ORDER BY Salary DESC) AS ranking # by Department
  FROM
    Employee e
  LEFT JOIN
    Department d
  ON
    e.DepartmentId = d.Id
  )
SELECT
  Department, Employee, Salary
FROM
  CTE
WHERE
  ranking <= 3 # Top 3 with same salary
;

---

178. Rank Scores

178. Rank Scores

MySQL

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');

---

Approach: Window Function 251 ms

MySQL

SELECT 
  Score,
    DENSE_RANK() OVER(ORDER BY Score DESC) AS `Rank`
FROM
  Scores
ORDER BY
    Score DESC;

---

Approach: NO Window Function 861 ms

Get the distinct score rank

MySQL

SELECT 
    DISTINCT Score
FROM 
    Scores
ORDER BY 
    Score DESC;

Get the rank

MySQL

SELECT(
    SELECT 
            COUNT(DISTINCT Score)
    FROM 
            Scores WHERE Score >= s.Score) AS 'Rank'
FROM 
    Scores s;

Get the Score and Rank

MySQL

SELECT Score,
    (
        SELECT 
            COUNT(DISTINCT Score)
        FROM 
            Scores WHERE Score >= s.Score) AS 'Rank'
FROM 
    Scores s
ORDER BY 
    Score DESC;

---

511. Game Play Analysis I

511. Game Play Analysis I

MySQL

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');

---

Approach: CTE and RANK

MySQL

WITH CTE AS
  (
    SELECT 
    player_id, 
        event_date,
        DENSE_RANK() OVER(PARTITION BY player_id ORDER BY event_date ASC) AS ranking
  FROM
    Activity
    )
SELECT
  player_id,
    event_date AS first_login
FROM
  CTE
WHERE
  ranking = 1
;

---

512. Game Play Analysis II

512. Game Play Analysis II

MySQL

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');

Approach: CTE and DENSE_RANK

MySQL

WITH CTE AS
  (
    SELECT
    player_id,
        device_id,
        DENSE_RANK() OVER (PARTITION BY player_id ORDER BY event_date ASC) AS ranking
  FROM
    Activity
    )
SELECT
  player_id,
    device_id
FROM
  CTE
WHERE
  ranking = 1
;

---

JOIN

---

175. Combine Two Tables

175. Combine Two Tables

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');

Approach: Using outer join [Accepted] 299 ms

Algorithm

Since the PersonId in table Address is the foreign key of table Person, we can join this two table to get the address information of a person.

Considering there might not be an address information for every person, we should use outer join instead of the default inner join.

MySQL

SELECT FirstName, LastName, City, State
FROM Person LEFT JOIN Address
ON Person.PersonId = Address.PersonId;

---

181. Employees Earning More Than Their Managers

181. Employees Earning More Than Their Managers

MySQL

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);

Approach: Using WHERE clause [Accepted] 289 ms

As this table has the employee’s manager information, we probably need to select information from it twice.

MySQL

SELECT
  e1.Name AS Employee
FROM
  Employee e1, Employee e2
WHERE
  e1.ManagerId = e2.Id
    AND
    e1.Salary > e2.Salary;

---

Approach: Using JOIN clause [Accepted] 300 ms

Actually, JOIN is a more common and efficient way to link tables together, and we can use ON to specify some conditions.

MySQL

SELECT
  e1.Name AS Employee
FROM
  Employee e1
INNER JOIN
  Employee e2 ON e1.ManagerId = e2.Id
WHERE
    e1.Salary > e2.Salary;

---

1587. Bank Account Summary II

1587. Bank Account Summary II

MySQL

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');

---

Approach

MySQL

SELECT u.name, SUM(t.amount) AS `balance`
FROM Users u
INNER JOIN Transactions t ON u.account=t.account
GROUP BY name
HAVING balance > 10000;

---

Duplicate Records

196. Delete Duplicate Emails

196. Delete Duplicate Emails

MySQL

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;

Approach: Using WHERE clause 2000 ms

– By joining this table with itself on the Email column, we can get the following code.

MySQL

SELECT 
    p1.*
FROM 
    Person p1, 
    Person p2
WHERE 
    p1.Email = p2.Email;

Then we need to find the bigger id having same email address with other records. So we can add a new condition to the WHERE clause like this.

MySQL

SELECT
  p1.*
FROM
  Person p1, Person p2
WHERE
  p1.email = p2.email
    AND
    p1.Id > p2.Id;

As we already get the records to be deleted, we can alter this statement to DELETE in the end.

MySQL

DELETE
  p1.*
FROM
  Person p1, Person p2
WHERE
  p1.email = p2.email
    AND
    p1.Id > p2.Id;

---

Approach: Using GROUP BY Statement 1437 ms

SELECT * FROM Person with Minimum Id

MySQL

SELECT 
  MIN(Id), 
  Email
FROM 
  Person
GROUP BY 
  Email;

DELETE Records not in the above SELECT query

MySQL

DELETE FROM
  Person
WHERE
  Id NOT IN
    (SELECT Id FROM
    (SELECT
      MIN(Id) AS Id,
      Email
    FROM
      Person
    GROUP BY
      Email)
  AS tmp);

---

182. Duplicate Emails

182. Duplicate Emails

MySQL

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;

---

Approach: Using Subquery 285 ms

MySQL

SELECT
  DISTINCT p1.Email
FROM
  Person p1, Person p2
WHERE
  p1.Email = p2.Email
    AND
    p1.Id <> p2.Id;

---

Approach: Using GROUP BY and a temporary table [Accepted] 253 ms

MySQL

SELECT
  Email
FROM
  Person
GROUP BY
  Email
HAVING 
  COUNT(Email) >= 2;

---

Approach: Using GROUP BY and HAVING condition [Accepted]

MySQL

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

---

SWAP

627. Swap Salary 204 ms

627. Swap Salary

MySQL

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');

Approach: Using IF function 200 ms

MySQL

UPDATE
  salary
SET 
  sex = IF(sex='f', 'm', 'f');

---

Approach: Using UPDATE and CASE…WHEN [Accepted] 208 ms

MySQL

UPDATE Salary
SET Sex = CASE sex
        WHEN 'm' THEN 'f'
        WHEN 'f' THEN 'm'
END;

---

626. Exchange Seats

626. Exchange Seats

MySQL

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');

---

Approach: CASE 270 ms

MySQL

SELECT 
  (CASE
    WHEN id % 2 = 0 THEN id - 1
        WHEN id % 2 = 1 AND id = length THEN id
        ELSE id + 1
  END) AS id,
    student
FROM
  seat,
    (SELECT
    COUNT(*) AS length
  FROM 
    seat
  ) AS seat_length
ORDER BY
  id;

---

Reformat

---

1179. Reformat Department Table 440 ms

1179. Reformat Department Table

MySQL

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

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

1777. Product’s Price for Each Store

MySQL

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');

---

Approach 394 ms

MySQL

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;

---

Difference

197. Rising Temperature

197. Rising Temperature

MySQL

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');

SELECT * FROM Weather;

---

Approach: Using Subquery and DATEDIFF() clause 408 ms

MySQL

SELECT
  w1.id
FROM
  Weather w1, weather w2
WHERE
  DATEDIFF(w1.RecordDate, w2.RecordDate) = 1 
    AND 
    w1.Temperature > w2.Temperature;

---

Approach: Using JOIN and DATEDIFF() clause [Accepted] 394 ms

Algorithm

MySQL uses DATEDIFF to compare two date type values.

So, we can get the result by joining this table weather with itself and use this DATEDIFF() function.

MySQL

SELECT
  w1.id
FROM
  Weather w1
JOIN
  Weather w2 ON DATEDIFF(w1.RecordDate, w2.RecordDate) = 1 
    AND
    w1.Temperature > w2.Temperature;

---

REGEXP

1517. Find Users With Valid E-Mails

1517. Find Users With Valid E-Mails

MySQL

DROP TABLE IF EXISTS Users;
Create table If Not Exists Users (user_id int, name varchar(30), mail varchar(50));
Truncate table Users;
insert into Users (user_id, name, mail) values ('1', 'Winston', 'winston@leetcode.com');
insert into Users (user_id, name, mail) values ('2', 'Jonathan', 'jonathanisgreat');
insert into Users (user_id, name, mail) values ('3', 'Annabelle', 'bella-@leetcode.com');
insert into Users (user_id, name, mail) values ('4', 'Sally', 'sally.come@leetcode.com');
insert into Users (user_id, name, mail) values ('5', 'Marwan', 'quarz#2020@leetcode.com');
insert into Users (user_id, name, mail) values ('6', 'David', 'david69@gmail.com');
insert into Users (user_id, name, mail) values ('7', 'Shapiro', '.shapo@leetcode.com');

SELECT * FROM Users;

Approach REGEXP 600 ms

MySQL

/*
A valid e-mail has a prefix name and a domain where: 

The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.' and/or dash '-'. The prefix name must start with a letter.
The domain is '@leetcode.com'.
*/


-- end with @leetcode.com: (@leetcode.com)$
-- start with valid letters: ^[a-z]{1}[a-z0-9_.-]*
SELECT *
FROM Users
WHERE mail REGEXP '^[a-z][a-z0-9_.-]*(@leetcode.com)$';

---

IN or NOT IN

183. Customers Who Never Order 373 ms

183. Customers Who Never Order

MySQL

DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Orders;
Create table If Not Exists Customers (Id int, Name varchar(255));
Create table If Not Exists Orders (Id int, CustomerId int);
Truncate table Customers;
insert into Customers (Id, Name) values ('1', 'Joe');
insert into Customers (Id, Name) values ('2', 'Henry');
insert into Customers (Id, Name) values ('3', 'Sam');
insert into Customers (Id, Name) values ('4', 'Max');
Truncate table Orders;
insert into Orders (Id, CustomerId) values ('1', '3');
insert into Orders (Id, CustomerId) values ('2', '1');

SELECT * FROM Customers;
SELECT * FROM Orders;

---

Approach: Using NOT IN Clause

MySQL

SELECT
  NAME AS Customers
FROM
  Customers
WHERE
  Customers.Id NOT IN 
    (
    SELECT 
      CustomerId
    FROM
      Orders
    );

---

IF, CASE

610. Triangle Judgement

610. Triangle Judgement

MySQL

DROP TABLE If Exists triangle;
Create table If Not Exists triangle (x int, y int, z int);
Truncate table triangle;
insert into triangle (x, y, z) values ('13', '15', '30');
insert into triangle (x, y, z) values ('10', '20', '15');

---

Approach: Using CASE statement 224 ms

MySQL

SELECT x, y, z, 
CASE
    WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
    ELSE 'No'
END AS 'triangle'
FROM triangle;

---

Approach: Using IF function 227 ms

MySQL

SELECT
  x,
    y,
    z,
    IF(x + y > z AND x + z > y AND z + y > x, 'Yes', 'No') AS triangle
FROM
  triangle;

---

Statistics

597. Friend Requests I: Overall Acceptance Rate

597. Friend Requests I: Overall Acceptance Rate

MySQL

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');

---

Approach: Subquery 471 ms

MySQL

SELECT ROUND(
  IFNULL(
  (SELECT COUNT(DISTINCT requester_id, accepter_id) FROM RequestAccepted)
  /
  (SELECT COUNT(DISTINCT sender_id, send_to_id) FROM FriendRequest), 
    0), 
2) AS 'accept_rate';

---

Approach 430 ms

MySQL

SELECT
  IFNULL(
    ROUND(
      COUNT(DISTINCT requester_id, accepter_id)
      /
      COUNT(DISTINCT sender_id, send_to_id), 2),
        0.00) AS accept_rate
FROM
  FriendRequest,
  RequestAccepted;

---

1661. Average Time of Process per Machine

1661. Average Time of Process per Machine

MySQL

Create table If Not Exists Activity (machine_id int, process_id int, activity_type ENUM('start', 'end'), timestamp float);
Truncate table Activity;
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('0', '0', 'start', '0.712');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('0', '0', 'end', '1.52');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('0', '1', 'start', '3.14');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('0', '1', 'end', '4.12');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('1', '0', 'start', '0.55');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('1', '0', 'end', '1.55');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('1', '1', 'start', '0.43');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('1', '1', 'end', '1.42');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('2', '0', 'start', '4.1');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('2', '0', 'end', '4.512');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('2', '1', 'start', '2.5');
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('2', '1', 'end', '5.0');

---

Approach 186 ms

MySQL

-- Split the start, end in a table
SELECT s.machine_id, s.process_id, e.timestamp, s.timestamp
FROM Activity s
INNER JOIN Activity e ON 
  (s.machine_id, s.process_id) = (e.machine_id, e.process_id)
    AND
    (s.activity_type='start')
    AND
    (e.activity_type='end');

-- Calculate the average time consumption
SELECT s.machine_id, ROUND(AVG(e.timestamp - s.timestamp), 3) AS processing_time
FROM Activity s
INNER JOIN Activity e ON 
  (s.machine_id, s.process_id) = (e.machine_id, e.process_id)
    AND
    (s.activity_type='start')
    AND
    (e.activity_type='end')
GROUP BY s.machine_id;

---

1173. Immediate Food Delivery I

1173. Immediate Food Delivery I

MySQL

Create table If Not Exists Delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date);
Truncate table Delivery;
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('1', '1', '2019-08-01', '2019-08-02');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('2', '5', '2019-08-02', '2019-08-02');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('3', '1', '2019-08-11', '2019-08-11');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('4', '3', '2019-08-24', '2019-08-26');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('5', '4', '2019-08-21', '2019-08-22');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('6', '2', '2019-08-11', '2019-08-13');

MySQL

SELECT ROUND(
  (SELECT COUNT(order_date)
  FROM Delivery
  WHERE order_date = customer_pref_delivery_date)
    /
    (SELECT COUNT(*) FROM Delivery)
    * 100
    , 2) AS immediate_percentage;

---

1435. Create a Session Bar Chart

1435. Create a Session Bar Chart

MySQL

Create table If Not Exists Sessions (session_id int, duration int);
Truncate table Sessions;
insert into Sessions (session_id, duration) values ('1', '30');
insert into Sessions (session_id, duration) values ('2', '199');
insert into Sessions (session_id, duration) values ('3', '299');
insert into Sessions (session_id, duration) values ('4', '580');
insert into Sessions (session_id, duration) values ('5', '1000');

---

Approach 370 ms

MySQL

-- No counting 0
SELECT
CASE
  WHEN 0 <= duration AND duration < 5*60 THEN '[0-5>'
  WHEN 5*60 <= duration AND duration < 10*60 THEN '[5-10>'
  WHEN 10*60 <= duration AND duration < 15*60 THEN '[10-15>'
  WHEN 15*60 <= duration THEN '[15 or more'
END AS bin,
COUNT(1) AS total
FROM Sessions
GROUP BY bin;

-- 
WITH cte AS (
    SELECT duration/60 AS mins
    FROM sessions
)

SELECT '[0-5>' AS bin,
       SUM(CASE WHEN mins<5 THEN 1 ELSE 0 END) AS total
FROM cte
UNION ALL
SELECT '[5-10>' AS bin,
       SUM(CASE WHEN mins>=5 AND mins<10 THEN 1 ELSE 0 END) AS total
FROM cte
UNION ALL
SELECT '[10-15>' AS bin,
       SUM(CASE WHEN mins>=10 AND mins<15 THEN 1 ELSE 0 END) AS total
FROM cte
UNION ALL
SELECT '15 or more' AS bin,
       SUM(CASE WHEN mins>=15 THEN 1 ELSE 0 END) AS total
FROM cte;

---

1211. Queries Quality and Percentage

1211. Queries Quality and Percentage

MySQL

Create table If Not Exists Queries (query_name varchar(30), result varchar(50), position int, rating int);
Truncate table Queries;
insert into Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', '1', '5');
insert into Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', '2', '5');
insert into Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', '1');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', '5', '2');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Siamese', '3', '3');
insert into Queries (query_name, result, position, rating) values ('Cat', 'Siamese', '7', '4');

MySQL

SELECT 
  query_name,
  ROUND(AVG(rating / position), 2) AS quality,
  ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage 
FROM 
  Queries
GROUP BY 
  query_name

---

1511. Customer Order Frequency

1511. Customer Order Frequency

MySQL

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');

---

Approach 716 ms

MySQL

-- Extract name, price, quantity, month
SELECT
  c.customer_id, c.name, p.price, o.quantity, DATE_FORMAT(o.order_date, '%Y-%m') AS month
FROM
  Customers c
INNER JOIN
  Product p
INNER JOIN
  Orders o
ON c.customer_id = o.customer_id
  AND p.product_id = o.product_id
ORDER BY 
  customer_id, name, month;


-- Group the above query by name, month
SELECT
  c.customer_id, c.name, SUM(p.price * o.quantity) AS transcript, DATE_FORMAT(o.order_date, '%Y-%m') AS month
FROM
  Customers c
INNER JOIN
  Product p
INNER JOIN
  Orders o
ON c.customer_id = o.customer_id
  AND p.product_id = o.product_id
GROUP BY 
  customer_id, name, month
ORDER BY 
  customer_id, name, month;


-- SELECT name who spent more than $100 in both June and July
SELECT
  c.customer_id, c.name
FROM
  Customers c
INNER JOIN
  Product p
INNER JOIN
  Orders o
ON c.customer_id = o.customer_id
  AND p.product_id = o.product_id
GROUP BY 
  customer_id, name
HAVING
  SUM(IF(DATE_FORMAT(o.order_date, '%Y-%m')='2020-06', quantity, 0) * price) >= 100
    AND
    SUM(IF(DATE_FORMAT(o.order_date, '%Y-%m')='2020-07', quantity, 0) * price) >= 100;

---

WHERE

---

1757. Recyclable and Low Fat Products

1757. Recyclable and Low Fat Products

MySQL

DROP TABLE IF EXISTS Products;

Create table If Not Exists Products (product_id int, low_fats ENUM('Y', 'N'), recyclable ENUM('Y','N'));
Truncate table Products;
insert into Products (product_id, low_fats, recyclable) values ('0', 'Y', 'N');
insert into Products (product_id, low_fats, recyclable) values ('1', 'Y', 'Y');
insert into Products (product_id, low_fats, recyclable) values ('2', 'N', 'Y');
insert into Products (product_id, low_fats, recyclable) values ('3', 'Y', 'Y');
insert into Products (product_id, low_fats, recyclable) values ('4', 'N', 'N');

SELECT * FROM Products;

---

Approach 428 ms

MySQL

SELECT product_id
FROM Products
WHERE low_fats = 'Y' AND recyclable = 'Y';

---

1141. User Activity for the Past 30 Days I

1141. User Activity for the Past 30 Days I

MySQL

Create table If Not Exists Activity (user_id int, session_id int, activity_date date, activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message'));
Truncate table Activity;
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'scroll_down');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-20', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'send_message');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'send_message');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'end_session');

---

Approach 353 ms

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE DATEDIFF('2019-07-27', activity_date) < 30
GROUP BY activity_date;

---

1142. User Activity for the Past 30 Days II

1142. User Activity for the Past 30 Days II

MySQL

Create table If Not Exists Activity (user_id int, session_id int, activity_date date, activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message'));
Truncate table Activity;
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'scroll_down');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-20', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'send_message');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'send_message');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '5', '2019-07-21', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '5', '2019-07-21', 'scroll_down');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '5', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'end_session');

---

Approach 443ms

MySQL

SELECT IFNULL(
  ROUND(COUNT(DISTINCT session_id)/COUNT(DISTINCT user_id), 2), 
    0) AS average_sessions_per_user
FROM Activity
WHERE DATEDIFF('2019-07-27', activity_date) < 30;

---

603. Consecutive Available Seats

603. Consecutive Available Seats

MySQL

Create table If Not Exists cinema (seat_id int primary key auto_increment, free bool);
Truncate table cinema;
insert into cinema (seat_id, free) values ('1', '1');
insert into cinema (seat_id, free) values ('2', '0');
insert into cinema (seat_id, free) values ('3', '1');
insert into cinema (seat_id, free) values ('4', '1');
insert into cinema (seat_id, free) values ('5', '1');
insert into cinema (seat_id, free) values ('6', '1');
insert into cinema (seat_id, free) values ('7', '1');
insert into cinema (seat_id, free) values ('8', '0');
insert into cinema (seat_id, free) values ('9', '1');
insert into cinema (seat_id, free) values ('10', '1');
insert into cinema (seat_id, free) values ('11', '1');
insert into cinema (seat_id, free) values ('12', '1');

MySQL

SELECT seat_id
FROM cinema 
WHERE free = 1
AND
  (
    seat_id+1 IN (SELECT seat_id FROM cinema WHERE free = 1)
  OR
  seat_id-1 IN (SELECT seat_id FROM cinema WHERE free = 1)
    );

---

620. Not Boring Movies

620. Not Boring Movies

MySQL

Create table If Not Exists cinema (id int, movie varchar(255), description varchar(255), rating float(2, 1));
Truncate table cinema;
insert into cinema (id, movie, description, rating) values ('1', 'War', 'great 3D', '8.9');
insert into cinema (id, movie, description, rating) values ('2', 'Science', 'fiction', '8.5');
insert into cinema (id, movie, description, rating) values ('3', 'irish', 'boring', '6.2');
insert into cinema (id, movie, description, rating) values ('4', 'Ice song', 'Fantacy', '8.6');
insert into cinema (id, movie, description, rating) values ('5', 'House card', 'Interesting', '9.1');

Approach 181 ms

MySQL

SELECT *
FROM cinema
WHERE id%2=1 AND description!='boring'
ORDER BY rating DESC;

---

1350. Students With Invalid Departments

1350. Students With Invalid Departments

MySQL

Create table If Not Exists Departments (id int, name varchar(30));
Create table If Not Exists Students (id int, name varchar(30), department_id int);
Truncate table Departments;
insert into Departments (id, name) values ('1', 'Electrical Engineering');
insert into Departments (id, name) values ('7', 'Computer Engineering');
insert into Departments (id, name) values ('13', 'Bussiness Administration');
Truncate table Students;
insert into Students (id, name, department_id) values ('23', 'Alice', '1');
insert into Students (id, name, department_id) values ('1', 'Bob', '7');
insert into Students (id, name, department_id) values ('5', 'Jennifer', '13');
insert into Students (id, name, department_id) values ('2', 'John', '14');
insert into Students (id, name, department_id) values ('4', 'Jasmine', '77');
insert into Students (id, name, department_id) values ('3', 'Steve', '74');
insert into Students (id, name, department_id) values ('6', 'Luis', '1');
insert into Students (id, name, department_id) values ('8', 'Jonathan', '7');
insert into Students (id, name, department_id) values ('7', 'Daiana', '33');
insert into Students (id, name, department_id) values ('11', 'Madelynn', '1');

---

Approach 670 ms

MySQL

SELECT id, name
FROM Students
WHERE department_id NOT IN (SELECT id FROM Departments);

---

1623. All Valid Triplets That Can Represent a Country

1623. All Valid Triplets That Can Represent a Country

MySQL

Create table If Not Exists SchoolA (student_id int, student_name varchar(20));
Create table If Not Exists SchoolB (student_id int, student_name varchar(20));
Create table If Not Exists SchoolC (student_id int, student_name varchar(20));
Truncate table SchoolA;
insert into SchoolA (student_id, student_name) values ('1', 'Alice');
insert into SchoolA (student_id, student_name) values ('2', 'Bob');
Truncate table SchoolB;
insert into SchoolB (student_id, student_name) values ('3', 'Tom');
Truncate table SchoolC;
insert into SchoolC (student_id, student_name) values ('3', 'Tom');
insert into SchoolC (student_id, student_name) values ('2', 'Jerry');
insert into SchoolC (student_id, student_name) values ('10', 'Alice');

MySQL

-- Union all records from three schools and give each a school label
SELECT
  student_id, student_name, 'A' AS school
FROM
  SchoolA
UNION ALL
SELECT
  student_id, student_name, 'B' AS school
FROM 
  SchoolB
UNION ALL
SELECT 
  student_id, student_name, 'C' AS school
FROM 
  SchoolC;

-- SELECT student from each table where they don't have a same name and same id
SELECT 
  a.student_name AS member_A, b.student_name AS member_B, c.student_name AS member_C
FROM
  SchoolA a, SchoolB b, SchoolC c
WHERE
  a.student_id != b.student_id
    AND
    a.student_id != c.student_id
    AND
    b.student_id != c.student_id
    AND
    a.student_name != b.student_name
    AND
    a.student_name != c.student_name
    AND
    b.student_name != c.student_name;

---

613. Shortest Distance in a Line

613. Shortest Distance in a Line

MySQL

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');

---

Approach 200 ms

MySQL

-- Get the distances of each two points
SELECT 
  p1.x, p2.x, ABS(p1.x - p2.x) AS distance
FROM 
  point p1
JOIN
  point p2
    ON
    p1.x != p2.x
;

-- Get the shortest distance: Solution 1
SELECT MIN(ABS(p1.x - p2.x)) AS shortest
FROM 
  point p1
JOIN
  point p2
    ON
    p1.x != p2.x
;

-- Get the shortest distance: Solution 2
SELECT 
  MIN(ABS(p1.x - p2.x)) AS shortest
FROM
  point p1, point p2
WHERE
  p1.x != p2.x
;

---

607. Sales Person

607. Sales Person

MySQL

Create table If Not Exists salesperson (sales_id int, name varchar(255), salary int,commission_rate int, hire_date varchar(255));
Create table If Not Exists company (com_id int, name varchar(255), city varchar(255));
Create table If Not Exists orders (order_id int, order_date varchar(255), com_id int, sales_id int, amount int);
Truncate table salesperson;
insert into salesperson (sales_id, name, salary, commission_rate, hire_date) values ('1', 'John', '100000', '6', '4/1/2006');
insert into salesperson (sales_id, name, salary, commission_rate, hire_date) values ('2', 'Amy', '12000', '5', '5/1/2010');
insert into salesperson (sales_id, name, salary, commission_rate, hire_date) values ('3', 'Mark', '65000', '12', '12/25/2008');
insert into salesperson (sales_id, name, salary, commission_rate, hire_date) values ('4', 'Pam', '25000', '25', '1/1/2005');
insert into salesperson (sales_id, name, salary, commission_rate, hire_date) values ('5', 'Alex', '5000', '10', '2/3/2007');
Truncate table company;
insert into company (com_id, name, city) values ('1', 'RED', 'Boston');
insert into company (com_id, name, city) values ('2', 'ORANGE', 'New York');
insert into company (com_id, name, city) values ('3', 'YELLOW', 'Boston');
insert into company (com_id, name, city) values ('4', 'GREEN', 'Austin');
Truncate table orders;
insert into orders (order_id, order_date, com_id, sales_id, amount) values ('1', '1/1/2014', '3', '4', '10000');
insert into orders (order_id, order_date, com_id, sales_id, amount) values ('2', '2/1/2014', '4', '5', '5000');
insert into orders (order_id, order_date, com_id, sales_id, amount) values ('3', '3/1/2014', '1', '1', '50000');
insert into orders (order_id, order_date, com_id, sales_id, amount) values ('4', '4/1/2014', '1', '4', '25000');

---

Approach 1 885 ms

MySQL

SELECT
    name
FROM 
    salesperson
WHERE 
    name NOT IN (
        SELECT
            s.name
        FROM
            salesperson s, orders o, company c
        WHERE
            s.sales_id = o.sales_id
            AND
            o.com_id = c.com_id
            AND
            c.name = 'RED'
        )
;

---

Approach 2 1253 ms

MySQL

SELECT
    s.name
FROM 
    salesperson s
LEFT JOIN
  orders o ON s.sales_id = o.sales_id
LEFT JOIN
  company c ON o.com_id = c.com_id
GROUP BY
  s.name
HAVING
  SUM(c.name = 'RED') = 0
    OR
    SUM(c.name = 'RED') IS NULL;

---

GROUP BY

---

596. Classes More Than 5 Students

596. Classes More Than 5 Students

MySQL

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');

---

Approach

MySQL

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

---

1241. Number of Comments per Post

1241. Number of Comments per Post

MySQL

Create table If Not Exists Submissions (sub_id int, parent_id int);
Truncate table Submissions;
insert into Submissions (sub_id, parent_id) values ('1', NULL);
insert into Submissions (sub_id, parent_id) values ('2', NULL);
insert into Submissions (sub_id, parent_id) values ('1', NULL);
insert into Submissions (sub_id, parent_id) values ('12', NULL);
insert into Submissions (sub_id, parent_id) values ('3', '1');
insert into Submissions (sub_id, parent_id) values ('5', '2');
insert into Submissions (sub_id, parent_id) values ('3', '1');
insert into Submissions (sub_id, parent_id) values ('4', '1');
insert into Submissions (sub_id, parent_id) values ('9', '1');
insert into Submissions (sub_id, parent_id) values ('10', '2');
insert into Submissions (sub_id, parent_id) values ('6', '7');

Approach 2510 ms

MySQL

SELECT
    DISTINCT sub_id AS post_id,
    (SELECT COUNT(DISTINCT sub_id) FROM Submissions s2 WHERE s1.sub_id = s2.parent_id) AS number_of_comments
FROM
    Submissions AS s1
WHERE parent_id IS NULL
ORDER BY sub_id;

---

1050. Actors and Directors Who Cooperated At Least Three Times

1050. Actors and Directors Who Cooperated At Least Three Times

MySQL

Create table If Not Exists ActorDirector (actor_id int, director_id int, timestamp int);
Truncate table ActorDirector;
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '0');
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '1');
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '1', '2');
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '2', '3');
insert into ActorDirector (actor_id, director_id, timestamp) values ('1', '2', '4');
insert into ActorDirector (actor_id, director_id, timestamp) values ('2', '1', '5');
insert into ActorDirector (actor_id, director_id, timestamp) values ('2', '1', '6');

---

Approach 409 ms

MySQL

SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(timestamp) >= 3;

---

1280. Students and Examinations

1280. Students and Examinations

MySQL

Create table If Not Exists Students (student_id int, student_name varchar(20));
Create table If Not Exists Subjects (subject_name varchar(20));
Create table If Not Exists Examinations (student_id int, subject_name varchar(20));
Truncate table Students;
insert into Students (student_id, student_name) values ('1', 'Alice');
insert into Students (student_id, student_name) values ('2', 'Bob');
insert into Students (student_id, student_name) values ('13', 'John');
insert into Students (student_id, student_name) values ('6', 'Alex');
Truncate table Subjects;
insert into Subjects (subject_name) values ('Math');
insert into Subjects (subject_name) values ('Physics');
insert into Subjects (subject_name) values ('Programming');
Truncate table Examinations;
insert into Examinations (student_id, subject_name) values ('1', 'Math');
insert into Examinations (student_id, subject_name) values ('1', 'Physics');
insert into Examinations (student_id, subject_name) values ('1', 'Programming');
insert into Examinations (student_id, subject_name) values ('2', 'Programming');
insert into Examinations (student_id, subject_name) values ('1', 'Physics');
insert into Examinations (student_id, subject_name) values ('1', 'Math');
insert into Examinations (student_id, subject_name) values ('13', 'Math');
insert into Examinations (student_id, subject_name) values ('13', 'Programming');
insert into Examinations (student_id, subject_name) values ('13', 'Physics');
insert into Examinations (student_id, subject_name) values ('2', 'Math');
insert into Examinations (student_id, subject_name) values ('1', 'Math');

---

Approach 657 ms

MySQL

SELECT Students.student_id, Students.student_name, Subjects.subject_name, COUNT(Examinations.student_id) AS attended_exams
FROM Students
INNER JOIN Subjects
LEFT JOIN Examinations
ON Students.student_id = Examinations.student_id AND Subjects.subject_name = Examinations.subject_name
GROUP BY student_id, student_name, subject_name
ORDER BY student_id;

---

1084. Sales Analysis III

1084. Sales Analysis III

MySQL

Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int);
Truncate table Product;
insert into Product (product_id, product_name, unit_price) values ('1', 'S8', '1000');
insert into Product (product_id, product_name, unit_price) values ('2', 'G4', '800');
insert into Product (product_id, product_name, unit_price) values ('3', 'iPhone', '1400');
Truncate table Sales;
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '2', '3', '2019-06-02', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '4', '2019-05-13', '2', '2800');

---

Approach

MySQL

SELECT product_id, product_name
FROM Product
INNER JOIN Sales
    USING (product_id)
GROUP BY product_id, product_name
HAVING 
  '2019-01-01' <= MIN(sale_date)
    AND
    MAX(sale_date) <= '2019-03-31'
;

---

1113. Reported Posts

1113. Reported Posts

MySQL

Create table If Not Exists Actions (user_id int, post_id int, action_date date, action ENUM('view', 'like', 'reaction', 'comment', 'report', 'share'), extra varchar(10));
Truncate table Actions;
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'view', 'None');
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'like', 'None');
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'share', 'None');
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '4', '2019-07-04', 'view', 'None');
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '4', '2019-07-04', 'report', 'spam');
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'view', 'None');
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'report', 'spam');
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'view', 'None');
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'report', 'spam');
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-04', 'view', 'None');
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-04', 'report', 'racism');
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-04', 'view', 'None');
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-04', 'report', 'racism');

---

Approach 505 ms

MySQL

SELECT
  extra AS report_reason, 
    COUNT(DISTINCT post_id) AS report_count
FROM
  Actions
WHERE
  DATEDIFF('2019-07-05', action_date) = 1
    AND
    action = 'Report'
GROUP BY
  report_reason
;

---

1083. Sales Analysis II

1083. Sales Analysis II

MySQL

Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int);
Truncate table Product;
insert into Product (product_id, product_name, unit_price) values ('1', 'S8', '1000');
insert into Product (product_id, product_name, unit_price) values ('2', 'G4', '800');
insert into Product (product_id, product_name, unit_price) values ('3', 'iPhone', '1400');
Truncate table Sales;
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '1', '3', '2019-06-02', '1', '800');
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '3', '2019-05-13', '2', '2800');

---

Approach 1008 ms

MySQL

SELECT 
  buyer_id
FROM 
  Sales s
INNER JOIN
  Product p
ON
  s.product_id = p.product_id
GROUP BY 
  buyer_id
HAVING 
  SUM(product_name = 'S8') > 0
    AND
    SUM(product_name = 'iPhone') = 0;

---

586. Customer Placing the Largest Number of Orders

586. Customer Placing the Largest Number of Orders

MySQL

Create table If Not Exists orders (order_number int, customer_number int);
Truncate table orders;
insert into orders (order_number, customer_number) values ('1', '1');
insert into orders (order_number, customer_number) values ('2', '2');
insert into orders (order_number, customer_number) values ('3', '3');
insert into orders (order_number, customer_number) values ('4', '3');

---

Approach 520 ms

MySQL

SELECT
  customer_number
FROM
  orders
GROUP BY
  customer_number
ORDER BY 
  COUNT(order_number) DESC
LIMIT 1;

---

1741. Find Total Time Spent by Each Employee

1741. Find Total Time Spent by Each Employee

MySQL

Create table If Not Exists Employees(emp_id int, event_day date, in_time int, out_time int);
Truncate table Employees;
insert into Employees (emp_id, event_day, in_time, out_time) values ('1', '2020-11-28', '4', '32');
insert into Employees (emp_id, event_day, in_time, out_time) values ('1', '2020-11-28', '55', '200');
insert into Employees (emp_id, event_day, in_time, out_time) values ('1', '2020-12-3', '1', '42');
insert into Employees (emp_id, event_day, in_time, out_time) values ('2', '2020-11-28', '3', '33');
insert into Employees (emp_id, event_day, in_time, out_time) values ('2', '2020-12-9', '47', '74');

---

Approach 434 ms

MySQL

SELECT
  event_day AS day,
  emp_id,
    SUM(out_time - in_time) AS total_time
FROM
  Employees
GROUP BY
  day,
    emp_id;

---

1731. The Number of Employees Which Report to Each Employee

1731. The Number of Employees Which Report to Each Employee

MySQL

Create table If Not Exists Employees(employee_id int, name varchar(20), reports_to int, age int);
Truncate table Employees;
insert into Employees (employee_id, name, reports_to, age) values ('9', 'Hercy', NULL, '43');
insert into Employees (employee_id, name, reports_to, age) values ('6', 'Alice', '9', '41');
insert into Employees (employee_id, name, reports_to, age) values ('4', 'Bob', '9', '36');
insert into Employees (employee_id, name, reports_to, age) values ('2', 'Winston', NULL, '37');

---

Approach 712 ms

MySQL

SELECT
  e1.reports_to AS employee_id,
    (SELECT name FROM Employees e2 WHERE e1.reports_to = e2.employee_id) AS name,
    COUNT(e1.reports_to) AS reports_count,
    ROUND(AVG(e1.age), 0) AS average_age
FROM
  Employees e1
GROUP BY
  1
HAVING
  reports_count > 0
ORDER BY
    employee_id
;

---

1212. Team Scores in Football Tournament

1212. Team Scores in Football Tournament

MySQL

DROP TABLE IF EXISTS Teams;
DROP TABLE IF EXISTS Matches;

Create table If Not Exists Teams (team_id int, team_name varchar(30));
Create table If Not Exists Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int);
Truncate table Teams;
insert into Teams (team_id, team_name) values ('10', 'Leetcode FC');
insert into Teams (team_id, team_name) values ('20', 'NewYork FC');
insert into Teams (team_id, team_name) values ('30', 'Atlanta FC');
insert into Teams (team_id, team_name) values ('40', 'Chicago FC');
insert into Teams (team_id, team_name) values ('50', 'Toronto FC');
Truncate table Matches;
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('1', '10', '20', '3', '0');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('2', '30', '10', '2', '2');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('3', '10', '50', '5', '1');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('4', '20', '30', '1', '0');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('5', '50', '30', '1', '0');

SELECT * FROM Teams;
SELECT * FROM Matches;

---

Approach 512 ms

MySQL

SELECT
  team_id,
    team_name,
    SUM(CASE WHEN team_id = host_team AND host_goals > guest_goals THEN 3 ELSE 0 END) +
    SUM(CASE WHEN team_id = host_team AND host_goals = guest_goals THEN 1 ELSE 0 END) +
    SUM(CASE WHEN team_id = guest_team AND guest_goals > host_goals THEN 3 ELSE 0 END) +
    SUM(CASE WHEN team_id = guest_team AND host_goals = guest_goals THEN 1 ELSE 0 END) AS num_points
FROM
  Teams
LEFT JOIN
  Matches ON team_id = host_team OR team_id = guest_team
GROUP BY
  team_id, team_name
ORDER BY
    num_points DESC,
    team_id ASC;

---

String

1667. Fix Names in a Table

1667. Fix Names in a Table

MySQL

Create table If Not Exists Users (user_id int, name varchar(40));
Truncate table Users;
insert into Users (user_id, name) values ('1', 'aLice');
insert into Users (user_id, name) values ('2', 'bOB');

---

Approach 678 ms

MySQL

-- SELECT 1st String
SELECT user_id, UPPER(SUBSTRING(name, 1, 1)) FROM Users;

-- SELECT
SELECT user_id, LOWER(SUBSTRING(name, 2)) FROM Users;

-- CONCAT
SELECT user_id, CONCAT(
  UPPER(SUBSTRING(name, 1, 1)), 
    LOWER(SUBSTRING(name, 2))
  ) AS name
FROM Users
ORDER BY user_id;

---

Date

1543. Fix Product Name Format

1543. Fix Product Name Format

MySQL

Create table If Not Exists Sales (sale_id int, product_name varchar(30), sale_date date);
Truncate table Sales;
insert into Sales (sale_id, product_name, sale_date) values ('1', '      LCPHONE', '2000-01-16');
insert into Sales (sale_id, product_name, sale_date) values ('2', ' LCPhone', '2000-01-17');
insert into Sales (sale_id, product_name, sale_date) values ('3', 'LcPhOnE   ', '2000-02-18');
insert into Sales (sale_id, product_name, sale_date) values ('4', '  LCKeyCHAiN   ', '2000-02-19');
insert into Sales (sale_id, product_name, sale_date) values ('5', '    LCKeyChain ', '2000-02-28');
insert into Sales (sale_id, product_name, sale_date) values ('6', ' Matryoshka ', '2000-03-31');

---

Approach 241ms

MySQL

-- Format product_name and sale_date
SELECT 
  LOWER(TRIM(product_name)) AS product_name, 
    DATE_FORMAT(sale_date, "%Y-%m") AS sale_date, 
    sale_id
FROM Sales;

-- GROUP BY
SELECT 
  LOWER(TRIM(product_name)) AS product_name, 
    DATE_FORMAT(sale_date, "%Y-%m") AS sale_date,
    COUNT(sale_id) AS total
FROM Sales
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC;

---

Window Function

---

1303. Find the Team Size

1303. Find the Team Size

MySQL

Create table If Not Exists Employee (employee_id int, team_id int);
Truncate table Employee;
insert into Employee (employee_id, team_id) values ('1', '8');
insert into Employee (employee_id, team_id) values ('2', '8');
insert into Employee (employee_id, team_id) values ('3', '8');
insert into Employee (employee_id, team_id) values ('4', '7');
insert into Employee (employee_id, team_id) values ('5', '9');
insert into Employee (employee_id, team_id) values ('6', '9');

---

Approach 244 ms

MySQL

SELECT
  employee_id,
    COUNT(*) OVER(PARTITION BY team_id) AS team_size
FROM
  Employee;

---

1596. The Most Frequently Ordered Products for Each Customer

1596. The Most Frequently Ordered Products for Each Customer

MySQL

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');

SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM Products;

---

Approach Window Function 1900 ms

MySQL

-- Window function, very slow
SELECT
  customer_id,
    product_id,
    product_name
FROM
  (
  SELECT
    customer_id,
    product_id,
    RANK() OVER(PARTITION BY customer_id ORDER BY COUNT(product_id) DESC) AS 'order_rank'
  FROM
    Orders
  GROUP BY
    customer_id,
    product_id
  ) AS tmp
JOIN
  Products USING(product_id)
WHERE order_rank = 1
ORDER BY
  customer_id,
    product_id;

---

Approach CTE 1800 ms

MySQL

-- CTE slow
WITH cte 
AS (
  SELECT 
    customer_id, 
    product_id, 
    COUNT(product_id) AS cnt
  FROM 
    Orders 
  GROUP BY 
    1,2
  )
SELECT 
  a.customer_id, 
    a.product_id, 
    p.product_name
FROM 
  cte a
JOIN 
  Products p USING(product_id)
WHERE 
  cnt IN (
      SELECT
        MAX(cnt)
      FROM
        cte b
      WHERE
        a.customer_id = b.customer_id
      )
ORDER BY
  customer_id,
    product_id;

---

UNION

---

1789. Primary Department for Each Employee

1789. Primary Department for Each Employee

MySQL

Create table If Not Exists Employee (employee_id int, department_id int, primary_flag ENUM('Y','N'));
Truncate table Employee;
insert into Employee (employee_id, department_id, primary_flag) values ('1', '1', 'N');
insert into Employee (employee_id, department_id, primary_flag) values ('2', '1', 'Y');
insert into Employee (employee_id, department_id, primary_flag) values ('2', '2', 'N');
insert into Employee (employee_id, department_id, primary_flag) values ('3', '3', 'N');
insert into Employee (employee_id, department_id, primary_flag) values ('4', '2', 'N');
insert into Employee (employee_id, department_id, primary_flag) values ('4', '3', 'Y');

---

Approach 495 ms

MySQL

SELECT
  employee_id,
    department_id
FROM
  Employee
WHERE
  employee_id NOT IN
  (SELECT
    employee_id
  FROM
    Employee
  WHERE
    primary_flag = 'Y')
UNION ALL
SELECT
  employee_id,
    department_id
FROM
  Employee
WHERE
  primary_flag = 'Y'
ORDER BY
  employee_id;

---

WITH RECURSION

---

1270. All People Report to the Given Manager

1270. All People Report to the Given Manager

Schema

MySQL

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');

Table Employees

MySQL

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
| manager_id    | int     |
+---------------+---------+

• employee_id is the primary key for this table.

• Each row of this table indicates that the employee with ID employee_id and name employee_name reports his work to his/her direct manager with manager_id

• The head of the company is the employee with employee_id = 1.

  Write an SQL query to find employee_id of all employees that directly or indirectly report their work to the head of the company.

The indirect relation between managers will not exceed 3 managers as the company is small.

Return result table in any order without duplicates.

The query result format is in the following example:

Employees table:

MySQL

+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1           | Boss          | 1          |
| 3           | Alice         | 3          |
| 2           | Bob           | 1          |
| 4           | Daniel        | 2          |
| 7           | Luis          | 4          |
| 8           | Jhon          | 3          |
| 9           | Angela        | 8          |
| 77          | Robert        | 1          |
+-------------+---------------+------------+

Result table:

MySQL

+-------------+
| employee_id |
+-------------+
| 2           |
| 77          |
| 4           |
| 7           |
+-------------+

The head of the company is the employee with employee_id 1.
The employees with employee_id 2 and 77 report their work directly to the head of the company.
The employee with employee_id 4 report his work indirectly to the head of the company 4 –> 2 –> 1.
The employee with employee_id 7 report his work indirectly to the head of the company 7 –> 4 –> 2 –> 1.
The employees with employee_id 3, 8 and 9 don’t report their work to head of company directly or indirectly.

---

Solution 1: With head and initial node

The curical concetp of MySQL Recursive CTE is the first non-recursive query and the rest recursive queries.

MySQL | Recursive CTE (Common Table Expressions) By Tanvi_Garg

Solution 1 non-recursive query: using the head as root node.

• employee_id: 1
• manager_id: 1

Solution 1 recursive query: the partent node from every pervious iteration.

Root Node

MySQL

-- Select the head as root node
SELECT 
  employee_id, 
    manager_id
FROM 
  Employees
WHERE 
  employee_id = 1;

employee_id	manager_id
1	1

---

Solution 1: Path

MySQL

-- Solution 1: Path
WITH RECURSIVE
CTE (employee_id, manager_id) AS -- recursive name is CTE
(
SELECT -- non-recursive: 1st query, the head
  employee_id,
    CAST(manager_id AS CHAR(30))
FROM 
  Employees
WHERE
  employee_id = 1
UNION ALL
SELECT -- recursive query: the rest queries and the path to the head
  e.employee_id,
    CONCAT(e.employee_id, '-->', c.manager_id)
FROM 
  CTE c
INNER JOIN Employees e ON c.employee_id = e.manager_id
HAVING 
  e.employee_id != 1
)
SELECT -- select specific rows from cte
  *
FROM 
  CTE
ORDER BY 
  employee_id;

employee_id	manager_id
1	1
2	2-->1
4	4-->2-->1
7	7-->4-->2-->1
77	77-->1

---

Solution 1: Answer

MySQL

-- Solution 1: Answer
WITH RECURSIVE
CTE (employee_id
  -- , manager_id
) AS -- recursive name is CTE
(
SELECT -- non-recursive: 1st query, the head
  employee_id
    -- , CAST(manager_id AS CHAR(30))
FROM 
  Employees
WHERE
  employee_id = 1
UNION ALL
SELECT -- recursive query: the rest queries and the path to the head
  e.employee_id
    -- , CONCAT(e.employee_id, '-->', c.manager_id)
FROM 
  CTE c
INNER JOIN Employees e ON c.employee_id = e.manager_id
HAVING 
  e.employee_id != 1
)
SELECT -- select specific rows from cte
  *
FROM 
  CTE
WHERE employee_id != 1 -- Filter the root node that is the head in solution 1
ORDER BY 
  employee_id;

employee_id

2

4

7

77

---

Solution 2: Without head and initial node

Solution 2 non-recursive query: the two second level nodes as root nodes.

• employee_id: 2 & 77 (employee_id != 1)
• manager_id: 1 (manager_id = 1)

Solution 2 recursive query: the partent node from every pervious iteration.

Root Node

MySQL

-- Select the two second level nodes as root nodes
SELECT 
  employee_id, 
    manager_id
FROM 
  Employees
WHERE 
  manager_id = 1 
  AND 
  employee_id != 1;

employee_id	manager_id
2	1
77	1

---

Solution 2: Path

MySQL

-- Solution 2: Path
WITH RECURSIVE
CTE (employee_id, manager_id) AS -- recursive name is CTE
(
SELECT -- non-recursive query: the two second level nodes as root nodes.
  employee_id,
    CAST(manager_id AS CHAR(30))
FROM 
  Employees
WHERE 
  manager_id = 1 
  AND 
  employee_id != 1
UNION ALL
SELECT -- recursive query: the rest queries and the path to the two second level nodes
  e.employee_id,
    CONCAT(e.employee_id, '-->', c.manager_id)
FROM 
  CTE c
INNER JOIN Employees e ON c.employee_id = e.manager_id
)
SELECT 
  *
FROM 
  CTE
ORDER BY 
  employee_id;

employee_id	manager_id
2	1
4	4-->1
7	7-->4-->1
77	1

---

Solution 2: Answer

MySQL

-- Solution 2: Answer
WITH RECURSIVE -- recursive name is CTE
CTE (employee_id
  -- , manager_id
    ) AS 
(
SELECT -- non-recursive query: the two second level nodes as root nodes.
  employee_id
    -- , CAST(manager_id AS CHAR(30))
FROM 
  Employees
WHERE 
  manager_id = 1 
  AND 
  employee_id != 1
UNION ALL
SELECT -- recursive query: the rest queries and the path to the two second level nodes
  e.employee_id
    -- , CONCAT(e.employee_id, '-->', c.manager_id)
FROM 
  CTE c
INNER JOIN Employees e ON c.employee_id = e.manager_id
)
SELECT
  *
FROM 
  CTE
ORDER BY 
  employee_id;

employee_id

2

4

7

77

---

Consecutive

180. Consecutive Numbers

180. Consecutive Numbers

Schema

MySQL

DROP TABLE IF EXISTS Logs;
Create table If Not Exists Logs (Id int, Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1', '1');
insert into Logs (Id, Num) values ('2', '1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4', '2');
insert into Logs (Id, Num) values ('5', '1');
insert into Logs (Id, Num) values ('6', '2');
insert into Logs (Id, Num) values ('7', '2');

Logs table:

MySQL

+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

---

Explanation

My idea is to join the Num three times.

• For the 1st table Logs l1, keep it unmodified.
• For the 2nd table Logs l2, move all Num downside 1 row, which is equal to add 1 to all Id.
• For the 3rd table Logs l3, move all Num downside 2 rows, which is equal to add 2 to all Id.

l1: SELECT Id, Num FROM Logs AS l1;

MySQL

+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

l2: SELECT Id+1 AS Id, Num FROM Logs AS l2;

MySQL

+----+-----+
| Id | Num |
+----+-----+
| 2  | 1   |
| 3  | 1   |
| 4  | 1   |
| 5  | 2   |
| 6  | 1   |
| 7  | 2   |
| 8  | 2   |
+----+-----+

l3: SELECT Id+1 AS Id, Num FROM Logs AS l2;

MySQL

+----+-----+
| Id | Num |
+----+-----+
| 3  | 1   |
| 4  | 1   |
| 5  | 1   |
| 6  | 2   |
| 7  | 1   |
| 8  | 2   |
| 9  | 2   |
+----+-----+

JOIN the three tables.
NOTICE we will lost the last 2 rows but it is absolutely fine. Because the last two rows only can be the part of consecutive numbers of the last third row. And the last two Num are in the last record of l2 and the last record of l3.

MySQL

SELECT
  l1.Num AS n1,
    l2.Num AS n2,
  l3.Num AS n3
FROM
  Logs l1
INNER JOIN 
  (SELECT Id+1 AS Id, Num FROM Logs) AS l2 ON l1.Id = l2.Id
INNER JOIN 
  (SELECT Id+2 AS Id, Num FROM Logs) AS l3 ON l1.Id = l3.Id;

n1	n2	n3
1	1	1
2	1	1
1	2	1
2	1	2
2	2	1

---

Approach: JOIN 376 ms faster than 90% of MySQL solutions

MySQL

SELECT
  DISTINCT l1.Num AS ConsecutiveNums -- Using DISTINCT to remove the duplicate records
FROM
  Logs l1
INNER JOIN 
  (SELECT Id+1 AS Id, Num FROM Logs) AS l2 ON l1.Id = l2.Id
INNER JOIN 
  (SELECT Id+2 AS Id, Num FROM Logs) AS l3 ON l1.Id = l3.Id
WHERE -- Filter the row that has three same records
  l1.Num = l2.Num AND l1.Num = l3.Num;

ConsecutiveNums
1

---

Approach 2: Using IN 474 ms

MySQL

SELECT 
  DISTINCT Num AS ConsecutiveNums
FROM
  Logs
WHERE 
  (Id + 1, Num) IN (SELECT * FROM Logs)
    AND
    (Id + 2, Num) IN (SELECT * FROM Logs)

---

Approach 3: Using WHERE 440 ms

MySQL

SELECT
  l1.Num AS n1,
    l2.Num AS n2,
  l3.Num AS n3
FROM
  Logs l1,
    Logs l2,
    Logs l3
WHERE
  l1.Id = l2.Id + 1
  AND
  l1.Id = l3.Id + 2;

MySQL

SELECT
  DISTINCT l1.Num AS ConsecutiveNums -- Using DISTINCT to remove the duplicate records
FROM
  Logs l1,
    Logs l2,
    Logs l3
WHERE
  l1.Id = l2.Id + 1
  AND
  l1.Id = l3.Id + 2
    AND
    l1.Num = l2.Num
    AND
    l1.Num = l3.Num;

---