LeetCode - 1934. Confirmation Rate (MySQL)

Medium, SQL - LEFT JOIN

RunTime : 566 ms

문제

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write a solution to find the confirmation rate of each user.

Return the result table in any order.

풀이

• confirmed면 +1 아님 +0의 로직을 통해 rate를 구할 수 있다.
• 처음엔 걍 COUNT 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 1

점점 SQL 못해지는 느낌