3주차 강의 join 연습

join을 할 때 어떤 테이블을 어떤 필드로 묶어줄 것인가에 대해
고민이 많이 필요하다는 것을 배웠다.

같은 필드 값이더라도
각 테이블에서 필드가 의미하거나 기준이 되는 것들이 다르기 때문에

보고자 하는 데이터에 맞춰
join으로 붙여주고
group by로 묶어줄 때도 어떤 테이블의 어떤 필드로 묶을 것인지 고민해야 한다는 것.

점점 알아갈 수록 쉽지 않지만
배우는 바도 있고 재미있는 것 같다.

실전에서 더 활용 해보고 싶다.

아래는 오늘 연습한 코드들

select co.title, ch.week, count(*) as cnt from courses co
inner join checkins ch on co.course_id = ch.course_id
inner join orders o on ch.course_id = o.course_id 
where o.created_at >= "2020-08-01"
group by co.title, ch.week 
order by co.title, ch.week 



SELECT * from point_users pu 

select u.name, count(*) as cnt from users u 
left join point_users pu on u.user_id = pu.user_id 
where pu.point_user_id is not NULL 
group by u.name



select count(pu.point_user_id) as pnt_user_cnt, count(*) as tot_user_cnt, round(count(pu.point_user_id)/count(*),2) as ratio 
from users u 
left join point_users pu on u.user_id = pu.user_id 
where u.created_at BETWEEN "2020-07-10" and "2020-07-20"



(
	select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1
	inner join checkins c2 on c1.course_id = c2.course_id
	inner join orders o on c2.user_id = o.user_id
	where o.created_at < '2020-08-01'
	group by c1.title, c2.week
)
union all
(
	select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1
	inner join checkins c2 on c1.course_id = c2.course_id
	inner join orders o on c2.user_id = o.user_id
	where o.created_at >= '2020-08-01'
	group by c1.title, c2.week
)

select ed.enrolled_id, e1.user_id, count(ed.done = 1) as cnt from enrolleds e1
inner join enrolleds_detail ed on e1.enrolled_id = ed.enrolled_id 
group by ed.enrolled_id 
order by cnt


select ed.enrolled_id, e1.user_id, count(*) as cnt from enrolleds e1
inner join enrolleds_detail ed on e1.enrolled_id = ed.enrolled_id 
where ed.done = 1
group by ed.enrolled_id, e1.user_id
order by cnt desc