Case와 Subquery

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

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

수강등록정보(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

수강등록정보(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

다른 방식으로...

select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id