[데이터 분석] 코드카타 90

90. Cofirmation Rate

https://leetcode.com/problems/confirmation-rate/

 

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.

The result format is in the following example.

 

SELECT

               a.user_id,

               ROUND(AVG(CASE WHEN b.action="confirmed" THEN 1 ELSE 0 END),2) confirmation_rate

FROM signups a LEFT JOIN confirmations b ON a.user_id = b.user_id

GROUP BY 1