Subquery 연습해보기 (where, select, from, inner join)

• Where 절에 들어가는 Subquery 연습해보기

1. 포인트가 평균보다 많은 사람들의 데이터를 추출해보자!
   *참고: 평균 포인트는 5380점

select * from point_users pu 
where pu.point > (select avg(pu2.point) from point_users pu2);

2. 이씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 데이터를 추출해보자!
   *참고: 이씨 성을 가진 유저들의 평균 포인트는 7454점
   

select * from point_users pu 
where pu.point > 
	(select avg(pu2.point) from point_users pu2
	inner join users u 
	on pu2.user_id = u.user_id 
	where u.name = "이**");

• Select 절에 들어가는 Subquery 연습해보기

1. checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
   

select checkin_id, course_id, user_id, likes, 
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) 
from checkins c;

2.checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

select checkin_id, c3.title, user_id, likes, 
(select round(avg(c2.likes),1) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses c3 
on c.course_id = c3.course_id;

• From 절에 들어가는 Subquery 연습해보기

1. course_id별 유저의 체크인 개수를 구해보기!

select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id

2.course_id별 인원을 구해보기!

select course_id, count(*) as cnt_total from orders
group by course_id

3. course_id별 checkin개수에 전체 인원을 붙이기
   

select a.course_id, b.cnt_checkins, a.cnt_total from
(
	select course_id, count(*) as cnt_total from orders
	group by course_id
) a
inner join (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) b
on a.course_id = b.course_id

4. 퍼센트를 나타내기
   

select a.course_id, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio from
(
	select course_id, count(*) as cnt_total from orders
	group by course_id
) a
inner join (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) b
on a.course_id = b.course_id

4-1. 강의 제목도 나타나면 좋겠네요!

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from
(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) a
inner join
(
	select course_id, count(*) as cnt_total from orders
	group by course_id 
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id