[LeetCode] MySQL 코딩연습

LeetCode에 있는 MySQL 문제를 풀어볼 예정이다.

문제를 다 적는것은 어렵고 문제 이름과 코드 링크만 남기려고 한다.

해커랭크보다 훨씬 UI가 편한 느낌..!!

 

175. Combine Two Tables

Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.

Return the result table in any order.

https://leetcode.com/problems/combine-two-tables/

SELECT A.firstName, A.lastName, B.city, B.state
FROM Person A
    LEFT OUTER JOIN Address B
    ON A.personId = B.personId
ORDER BY A.personId;

 

176. Second Highest Salary

Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

(Employee 테이블에서 두 번째로 salary가 높은 사람을 구하세요. 만약 두 번째로 높은 salary이 사람이 없다면 null을 출력하세요.)

https://leetcode.com/problems/second-highest-salary/

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

* OFFSET - 특정 값으로 정렬하여 몇 번째 데이터를 구할 때 사용한다.

 

177. Nth Highest Salary

Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

(Employee 테이블에서 n번째로 salary가 높은 사람을 구하세요. 만약 n번째로 높은 salary의 사람이 없다면 null을 출력하세요.)

https://leetcode.com/problems/nth-highest-salary/

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN   
  SET N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT salary 
      FROM Employee 
      ORDER BY salary DESC
      LIMIT 1 OFFSET N      
  );
END

 

178. Rank Scores

Write an SQL query to rank the scores. The ranking should be calculated according to the following rules:

• The scores should be ranked from the highest to the lowest.
• If there is a tie between two scores, both should have the same ranking.
• After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks.

Return the result table ordered by score in descending order.

(Scores 테이블에서 score가 높은 순으로 랭크를 매기세요. 만약 score 2개가 동일하다면 랭킹은 동일해야 합니다. 동일한 rank 다음에 오는 score의 경우 연속적인 정수여야 합니다.)

https://leetcode.com/problems/rank-scores/

SELECT A.score, B.rank
FROM Scores A
    LEFT OUTER JOIN (
        SELECT score
            , @rownum := @rownum + 1 AS 'rank'
        FROM (
            SELECT DISTINCT score       
            FROM Scores IA
            ) A
            JOIN (
                SELECT @rownum := 0
            ) IB
        ORDER BY score DESC
    ) B
    ON A.score = B.score 
ORDER BY A.score DESC

-- DENSE_RANK() 
SELECT Score
    , DENSE_RANK() OVER (ORDER BY Score DESC) AS 'rank'
FROM Scores;

* DENSE_RANK() - 순서대로 랭크를 매길 때 사용한다. 만약 동일한 값이 있을 경우에는 동일한 랭크가 되며 다음 값은 연속되는 정수로 랭크가 매겨진다. 예를 들어, 아래처럼 100의 랭크값이 1인 경우 다음 200은 4가 아닌 2가 된다. 

 

100 1

100 1

100 1

200 2

 

180. Consecutive Numbers

Write an SQL query to find all numbers that appear at least three times consecutively.

(Logs 테이블에서 연속되는 3개의 id값이 동일한 경우를 찾으세요.)

https://leetcode.com/problems/consecutive-numbers/

--8분
SELECT DISTINCT A.num AS 'ConsecutiveNums'
FROM Logs A
    JOIN Logs B
    ON A.id+1 = B.id
    JOIN Logs C
    ON A.id+2 = C.id 
WHERE A.num = B.NUM
    AND B.num = C.num;

 

181. Employees Earning More Than Their Managers

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

(Employee 테이블에서 emplayee가 자신의 manager보다 salary가 높은 경우를 찾으세요.)

https://leetcode.com/problems/employees-earning-more-than-their-managers/

--3분
SELECT A.name AS 'Employee'
FROM Employee A
    JOIN Employee B
    ON A.managerId = B.id   
    AND A.salary > B.salary;

 

182. Duplicate Emails

Write an SQL query to report all the duplicate emails.

Return the result table in any order.

(Person 테이블에서 중복되는 email를 모두 찾으세요.)

https://leetcode.com/problems/duplicate-emails/

-- 1분
SELECT email       
FROM Person
GROUP BY email       
HAVING COUNT(*) > 1;

 

183. Customers Who Never Order

Write an SQL query to report all customers who never order anything.

Return the result table in any order.

(Customers 테이블에서 한번도 주문한 적이 없는 customer를 찾으세요.)

https://leetcode.com/problems/customers-who-never-order/

-- 2분
SELECT A.name AS 'Customers'
FROM Customers A
    LEFT OUTER JOIN Orders B
    ON A.ID = B.customerId
WHERE B.customerId IS NULL;

 

184. Department Highest Salary

Write an SQL query to find employees who have the highest salary in each of the departments.

Return the result table in any order.

(각 부서별로 salary가 가장 높은 employee를 찾으세요.)

https://leetcode.com/problems/department-highest-salary/

-- 16분
SELECT C.name AS 'Department' 
    , A.name AS 'Employee'
    , B.max_salary AS 'Salary'
FROM Employee A
    JOIN (
        SELECT departmentId, MAX(salary) AS 'max_salary'
        FROM Employee 
        GROUP BY departmentId
    ) B
    ON A.salary = B.max_salary
    AND A.departmentId = B.departmentId  --!!
    JOIN Department C
    ON A.departmentId = C.id;

 

185. Department Top Three Salaries

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write an SQL query to find the employees who are high earners in each of the departments.

Return the result table in any order.

(각 부서별로 가장 높은 salary를 받는 3명을 찾으세요.)

https://leetcode.com/problems/department-top-three-salaries/

-- 13분
SELECT C.name AS 'Department'
    , A.name AS 'Employee'
    , A.salary AS 'Salary'
FROM Employee A
    JOIN (
        SELECT id, departmentId, salary 
            , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS 'rank'
        FROM Employee 
    ) B
    ON A.id = B.id
    JOIN Department C
    ON A.departmentId = C.id
WHERE B.rank in (1, 2, 3);

 

196. Delete Duplicate Emails

Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id.

Return the result table in any order.

(Person 테이블에서 중복되는 email을 1개만 제외하고 삭제하세요. 제외하는 1개의 email은 가장 작은 id값입니다.)

https://leetcode.com/problems/delete-duplicate-emails/

--13분
DELETE A FROM Person A
WHERE id NOT IN (
     SELECT id
     FROM (SELECT MIN(id) AS 'id' FROM Person GROUP BY email) IA     
    )

아무리 봐도 틀린 부분이 없는데 에러가 발생했다.

검색해보니 MySQL에서는 UPDATE, DELETE 구문에서 자기 테이블을 바로 사용하지 못한다고 한다.

그래서 원래 NOT IN 안에 들어갈 쿼리를 서브쿼리로 하나 작성해서 FROM 절에 사용하는 방식으로 해야 한다.

 

197. Rising Temperature

Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

(전일보다 기온이 높은 날짜를 모두 구세요.)

https://leetcode.com/problems/rising-temperature/

-- 3분
SELECT b.id
FROM Weather A
    JOIN Weather B
    ON A.recordDate = DATE_ADD(B.recordDate, INTERVAL -1 DAY)
    AND A.temperature < B.temperature

 

262. Trips and Users

The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Write a SQL query to find 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". Round Cancellation Rate to two decimal points.

Return the result table in any order.

(Trips 테이블에는 각 여행에 대한 정보가 저장되어 있습니다. 2013-10-01 ~ 2013-10-03일까지의 취소율을 구하세요. 취소율은 취소건수 / 전체건수로 구할 수 있으며, 모든 건수에서 client나 driver가 ban이 된 경우는 제외해야 합니다.)

https://leetcode.com/problems/trips-and-users/

--20분
SELECT A.request_at AS 'Day'
    , IFNULL(ROUND(CONVERT(cancel_cnt/total_cnt, DECIMAL(18,2)),2), 0.00) AS 'Cancellation Rate'
FROM (
    SELECT request_at, COUNT(*) AS 'total_cnt'
    FROM Trips
    WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
        AND client_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
        AND driver_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
    GROUP BY request_at
    ) A
    LEFT OUTER JOIN (
    SELECT request_at, COUNT(*) AS 'cancel_cnt'
    FROM Trips
    WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
        AND client_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
        AND driver_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
        AND status not in ('completed')
    GROUP BY request_at    
    ) B
    ON A.request_at = B.request_at
ORDER BY A.request_at

--2차 12분
SELECT A.request_at AS 'Day'
    , CAST(CASE WHEN total_cnt = 0 THEN 0 ELSE IFNULL(cancel_cnt,0) / total_cnt END AS DECIMAL(10,2)) AS 'Cancellation Rate'
FROM (
    SELECT request_at, COUNT(*) AS 'total_cnt'
    FROM Trips
    WHERE client_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
        AND driver_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
        AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY request_at
    ) A
    LEFT OUTER JOIN (
    SELECT request_at, COUNT(*) AS 'cancel_cnt'
    FROM Trips
    WHERE client_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
        AND driver_id NOT IN (SELECT users_id FROM Users WHERE banned = 'Yes')
        AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
        AND status LIKE 'cancelled%'
    GROUP BY request_at
    ) B
    ON A.request_at = B.request_at
ORDER BY A.request_at

 

595. Big Countries

A country is big if:

• it has an area of at least three million (i.e., 3000000 km2), or
• it has a population of at least twenty-five million (i.e., 25000000).

Write an SQL query to report the name, population, and area of the big countries.

Return the result table in any order.

(나라의 크기가 적어도 3000000 이상이거나 인구가 적어도 25000000 이상인 big country를 구하세요.)

https://leetcode.com/problems/big-countries/

-- 2분
SELECT name, population, area
FROM World
WHERE area >= 3000000
    OR population >= 25000000;

 

596. Classes More Than 5 Students

Write an SQL query to report all the classes that have at least five students.

Return the result table in any order.

(적어도 5명의 학생을 가지고 있는 class를 출력하세요.)

https://leetcode.com/problems/classes-more-than-5-students/

SELECT class
FROM Courses
GROUP BY class    
HAVING COUNT(*) >= 5;

 

601. Human Traffic of Stadium

Write an SQL query to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each.

Return the result table ordered by visit_date in ascending order.

(people의 수가 100명 이상인 3개 이상의 연속적인 id를 구하세요.)

https://leetcode.com/problems/human-traffic-of-stadium/

/*
	50분(다른거하다가더오래걸림;;)
    쿼리가 좀 별로이긴 한데 생각이 다른 방법은 생각이 안남;;;ㅠ
*/

SELECT *
FROM (
    SELECT A.id, A.visit_date, A.people
    FROM (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) A
        JOIN (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) B
        ON A.id = B.id-1
        JOIN (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) C
        ON B.id = C.id-1
    GROUP BY A.id, A.visit_date, A.people
    UNION
    SELECT B.id, B.visit_date, B.people
    FROM (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) A
        JOIN (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) B
        ON A.id = B.id-1
        JOIN (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) C
        ON B.id = C.id-1
    GROUP BY B.id, B.visit_date, B.people
    UNION
    SELECT C.id, C.visit_date, C.people
    FROM (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) A
        JOIN (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) B
        ON A.id = B.id-1
        JOIN (
        SELECT *
        FROM Stadium 
        WHERE people >= 100
        ) C
        ON B.id = C.id-1
    GROUP BY C.id, C.visit_date, C.people
) RESULT
ORDER BY id

연속적인 id값이 3개 이상이어야 하므로 people >= 100이상인 값을 대상으로 하여 3번 join 연산을 하였다. 이 경우 id = 5,6,7 과 id = 6,7,8 이렇게 2개의 row가 결과로 나오는데 최종 결과는 5,6,7,8이 나와야 하므로, join 테이블 a,b,c를 모두 union(중복제거)한 후 id로 정렬하였다. (연속적인 어쩌구 문제는 어려움...)

 

 

620. Not Boring Movies

Write an SQL query to report the movies with an odd-numbered ID and a description that is not "boring".

Return the result table ordered by rating in descending order.

(id가 홀수값이고 description이 "boring이 아닌 영화를 구하세요. rating 값을 내림차순으로 정렬하세요.)

https://leetcode.com/problems/not-boring-movies/

-- 4분
SELECT *
FROM Cinema
WHERE MOD(id,2) = 1
    AND description <> 'boring'
ORDER BY rating DESC;

 

626. Exchange Seats

Write an SQL query to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

(연속되는 2개의 seat id를 서로 교체하세요. 즉, 1번과 2번의 자리를 교체하고 3번과 4번의 자리를 교체해야 합니다. 만약 학생의 id가 홀수라면 마지막 학생의 id는 변경되지 않습니다.)

 

https://leetcode.com/problems/exchange-seats/

-- 21분
SELECT id, student
FROM (
    SELECT A.id AS 'id'
        , IFNULL(B.student,A.student) AS 'student'
    FROM (
            SELECT id, student
            FROM Seat 
            WHERE MOD(id,2) = 1
        ) A
        LEFT OUTER JOIN (
            SELECT id, student
            FROM Seat 
            WHERE MOD(id,2) = 0
        ) B
        ON A.id = B.id-1
    UNION 
    SELECT D.id AS 'id'
        , IFNULL(C.student,D.student)  AS 'student'
    FROM (
            SELECT id, student
            FROM Seat 
            WHERE MOD(id,2) = 1
        ) C
        JOIN (
            SELECT id, student
            FROM Seat 
            WHERE MOD(id,2) = 0
        ) D
        ON C.id = D.id-1
) T
ORDER BY id ;

 

627. Swap Salary

Write an SQL query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.

Note that you must write a single update statement, do not write any select statement for this problem.

(sex 컬럼의 값이 f이면 m으로 m이면 f로 변경하세요. 이 때 하나의 update 구문으로 작성해야 하고 중간 테이블 사용이 불가합니다. 또 어떤 select 구문으로 사용하지 마세요.)

https://leetcode.com/problems/swap-salary/

-- 3분
UPDATE Salary
SET sex = CASE sex WHEN 'm' THEN 'f' WHEN 'f' THEN 'm' END