[개발일지] SQL 입문 4주차-3

1. QUIZ

  1-1. 평균 이상 포인트를 가지고 있으면 '잘하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!

       SELECT pu.user_id,
                 pu.point,
                 (CASE when pu.point > (select avg(point) from point_users) then '잘하고있어요'
                          else '조금 더 화이팅!' end) as msg
         FROM point_users pu 

 

  1-2. 이메일 도메인별 유저의 수 세어보기

    1) subquery 미활용

       SELECT SUBSTRING_INDEX(u.email,'@',-1) as domain,
                 count(*) as cnt_domain 
          FROM users u 
       group by domain

 

    2) subquery 활용

       SELECT domain, count(*) as cnt FROM (
                                                          SELECT SUBSTRING_INDEX(email,'@',-1) as domain from users

                                                          ) a 
       group by domain

 

select a.enrolled_id,
       a.done_cnt,
       b.total_cnt
FROM(select e.enrolled_id, count(*) as done_cnt
from enrolleds_detail e
            where e.done = 1
            group by e.enrolled_id) a
      inner join
        (select e2.enrolled_id, count(*) as total_cnt
         from enrolleds_detail e2
            group by e2.enrolled_id) b on a.enrolled_id = b.enrolled_id
             

 

 

with table1 as (select e.enrolled_id, count(*) as done_cnt
from enrolleds_detail e
            where e.done = 1
            group by e.enrolled_id
               ),
     table2 as (select e2.enrolled_id, count(*) as total_cnt
             from enrolleds_detail e2
                group by e2.enrolled_id
               )   
SELECT a.enrolled_id, 
   a.done_cnt,
   b.total_cnt,
   round(a.done_cnt/b.total_cnt,2) as ratio from table1 a
   inner join table2 b on a.enrolled_id = b.enrolled_id