SQL 3주차 수업

#1 여러 테이블의 정보를 연결하는 join

두 테이블의 공통된 정보(key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보여준다

 

inner join  : 교집합의 정보를 내보내준다

left join : 하나의 정보를 왼쪽의 정보에 붙여서 내보내준다

따라서, 어디에 뭐를 붙일 것인지 순서가 중요하다!

 

#2 inner join 본격 쿼리 작성해보기(+ where, group by, order by, inner join)

결제 수단 별 유저 포인트의 평균값 구해보기

select o.payment_method, round(AVG(p.point),0) from point_users p
inner join orders o 
on p.user_id = o.user_id 
group by o.payment_method

 

결제하고 시작하지 않은 유저들을 성씨별로 세어보기

select name, count(*) as cnt_name from enrolleds e
inner join users u
on e.user_id = u.user_id 
where is_registered = 0
group by name
order by cnt_name desc

 

과목 별로 시작하지 않은 유저들을 세어보기

select c.course_id, c.title, count(*) as cnt_notstart from courses c
inner join enrolleds e 
on c.course_id = e.course_id
where is_registered = 0
group by c.course_id

 

웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보고, 보기 좋게 정리해보기

select c1.title, c2.week, count(*) as cnt from checkins c2
inner join courses c1 on c2.course_id = c1.course_id
group by c1.title c2.week
order by c1.title, c2.week

 

연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내보기

select 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
order by c1.title, c2.week

 

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

 

#3 left join 연습하기

유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계!

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

 

select name, count(*) 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 name

 

7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율 알아내보기

select count(point_user_id) as pnt_user_cnt,
       count(*) as tot_user_cnt,
       round(count(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'

 

#4 결과물 합치는 union

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