리트코드 SQL 50

 

 

 

이번에는 SQL 복습을 위해 리트코드 SQL 50제에서 문제를 풀어보았다.

 

해커랭크, 리트코드, 프로그래머스에 있는 대부분의 SQL문제는 풀어보았는데도 예상치 못한(풀어보지 못한) 문제가 나와서 당황했던 기억이 있다. 그래서 직접 풀이한 코드와 다른 사람들이 작성한 코드를 보며 복습해보기로 했다.

 

추후에는 데이터분석을 위한 SQL 레시피 책을 통해 공부할 예정이다.

 

 

1934. Confirmation Rate

# 내풀이
WITH total AS 
(
SELECT  user_id,
        SUM(CASE WHEN action = 'timeout' THEN 0
            ELSE 1 end) AS confirmation,
        COUNT(*) AS confirmation_cnt
  FROM confirmations       
GROUP BY 1)

SELECT s.user_id,
       IFNULL(ROUND(t.confirmation / t.confirmation_cnt, 2), 0) AS confirmation_rate
  FROM signups s LEFT JOIN total t ON s.user_id = t.user_id
ORDER BY 2 

# 다른사람 풀이
SELECT s.user_id, ROUND(AVG(IF(c.action="confirmed",1,0)),2) AS confirmation_rate
FROM Signups AS s LEFT JOIN Confirmations AS c ON s.user_id= c.user_id GROUP BY user_id;

 

• 쉬운 문제인데 WITH를 쓰면서 너무 돌아돌아 풀었던 것 같다. 파이썬 처럼 IF문 사용하면 되는데, CASE WHEN을 써가며 풀었던 것 같다.

 

 

550. Game Play Analysis IV

SELECT 
        ROUND((SELECT count(*)
                 FROM activity
                WHERE (player_id, event_date) 
                       IN 
                      (SELECT player_id,
                              DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as event_date
                         FROM activity
                     GROUP BY player_id))/COUNT(DISTINCT(player_id)),2) AS fraction
FROM activity

• 가독성있는 쿼리에 대해 더 연습해봐야겠다.

 

 

1204. Last Person to Fit in the Bus

WITH sum_sal AS
(
SELECT * 
       ,SUM(weight) OVER(ORDER BY turn) AS TotalWeight
  FROM queue
ORDER BY turn
)

SELECT person_name
  FROM sum_sal
 WHERE TotalWeight <= 1000
ORDER BY TotalWeight DESC
LIMIT 1

 

 

1341. Movie Rating

WITH summary AS 
( 
    (
SELECT  u.name
        ,COUNT(*) AS cnt_movie
  FROM movierating m JOIN users u ON m.user_id = u.user_id 
GROUP BY u.name
ORDER BY 2 DESC, 1 ASC
LIMIT 1
)

UNION ALL

(
SELECT  v.title
        ,AVG(m.rating) AS rating_movie
  FROM movierating m JOIN movies v ON m.movie_id = v.movie_id 
WHERE m.created_at LIKE '2020-02%'
GROUP BY v.title
ORDER BY 2 DESC, 1 
LIMIT 1
    )
)

SELECT name AS results
  FROM summary

 

 

1321. Restaurant Growth

SELECT
       visited_on
      ,(
        SELECT SUM(amount)
          FROM Customer
         WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
      ) AS amount
      ,ROUND((
        SELECT SUM(amount)/7
          FROM Customer
         WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
      ),2) AS average_amount
  FROM Customer c
 WHERE visited_on >= (
        SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
        FROM customer
    )
 GROUP BY visited_on

• DATE_SUB, ADD에 대해 기억해 두어야 함.

 

 

602. Friend Requests II: Who Has the Most Friends

WITH total_id AS 
(
    SELECT 
            r.requester_id AS id
           ,COUNT(*) AS cnt
      FROM RequestAccepted r
    GROUP BY 1

    UNION ALL  

    SELECT 
            r.accepter_id AS id
           ,COUNT(*) AS cnt 
      FROM RequestAccepted r
    GROUP BY 1
    )

SELECT 
       id
      ,SUM(cnt) AS num
FROM total_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1

 

 

 

185. Department Top Three Salaries

WITH RNK AS
(
SELECT 
      d.name AS Department
     ,e.name AS Employee
     ,e.salary AS Salary
     ,DENSE_RANK() OVER(PARTITION BY d.name ORDER BY e.salary DESC) AS Salary_rnk
  FROM Employee e join Department d on e.departmentId = d.id
)

SELECT 
        Department
       ,Employee
       ,Salary
  FROM RNK 
 WHERE Salary_rnk <= 3

 

 

 

Medium 이상 난이도 중 기억에 남겨야할 문제들 몇개만 추려서 작성해보았다. 하루에 몇문제라도 꾸준히 하는게 중요한 것 같다.