[SQL] 문제풀이_ Confirmation Rate

A. Confirmation Rate

1. 논리 펼치기 

1) signups를 기준으로 confirmations left join

2) singups의 user_id 별로 그룹 묶기

3) action이 confirmed인 것을 평균 구하기

4) round처리하기

 

2. 첫 번째 코드

# Write your MySQL query statement below
select distinct s.user_id
        , sum(if c.action = "confirmed " then 1 else 0)/count(s.user_id)
from signups s
left join confirmations c
on s.user_id = c.user_id

결과 : Runtime Error

You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax 
to use near 'c.action = "confirmed " then 1 else 0)/count(s.user_id)
from signups s
left join' at line 3

 

2. 두 번째 코드

# Write your MySQL query statement below
select distinct s.user_id
        ,round(avg(if(c.action = "confirmed ",1,0)),2) as confirmation_rate
from signups s
left join confirmations c
on s.user_id = c.user_id
group by s.user_id

결과: Accepted

 

B. 주의할 점

SQL 은 Python과 달리 if문을 사용하는 방법이 다르다.