문제 1) 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
select pu.point_user_id, pu.point,
case
when pu.point > (select avg(pu2.point) from point_users pu2) then '잘 하고 있어요!'
else '열심히 합시다!'
end as 'msg'
from point_users pu
문제 2) 이메일 도메인별 유저의 수 세어보기
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain
문제 3) '화이팅'이 포함된 오늘의 다짐만 출력해보기
select * from checkins c
where c.comment like '%화이팅%'
문제 4) 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
with lecture_done as (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
)
select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id
문제 5) 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기
with table1 as (
select enrolled_id, count(*) as done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as total_cnt from enrolleds_detail
group by 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