22.0328 SQL week i learned

LEFT JOIN : NULL
: 데이터에 비어있는 필드가 있을 경우에 출력이 필요할 때

select count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_user_cnt,
round(count(pu.point_user_id)/count(u.user_id),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'

UION
:비어있는 필드가 있는 데이터가 없다, 같은 값을 두 테이블에서 모두 가지고 있는 데이터만 출력
: order by 는 부분에 먹히지 않아, 합쳐진 전체에서 다시 order by 해야한다

(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)

HOME WORK
enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고,
완료한 강의 수가 많은 순서대로 정렬해보기.
user_id도 같이 출력되어야 한다 < ★주의

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