スパルタエンコーディングクラブは、エクセルよりも簡単で、SQL 3週間の開発ログ

	select * from users u
    inner join point_users pu
    	on u.user_id = pu.user_id

	1) checkins과 courses 테이블을 join해서 통계치 내보기
		select c1.course_id, count(*) from checkins c1
        inner join courses c2
            on c1.course_id = c2.course_id
        group by c1.course_id

	2) point_users와 users 테이블 join해서 point순으로 정렬
    	select pu.user_id, u.name, u.email, pu.point
        	from point_users pu
        inner join users u
        	on pu.user_id = u.user_id
		order by pu.point desc
        
	3) naver 이메일을 사용하는 유저들의 주문 수를 성씨별로 보기
		select u.name, count(*) as cnt 
        	from orders o
        inner join users u
        	on o.user_id = u.user_id
        where o.email like '%naver.com'
        group by u.name
        
    quiz 1) 결제수단별 유저포인트의 평균값
    	select o.payment_method, round(avg(pu.point), 2) as avg_point
        	from point_users pu
        inner join orders o
        	on pu.user_id = o.user_id
        group by o.payment_method
	
    quiz 2) 성씨별 결제하고 시작하지 않은 유저의 수
    	select u.name, count(*) as cnt
        	from enrolled e
        inner join users u
        	on e.user_id = u.user_id
        where e.is_registered = 0
        group by u.name
        order by cnt desc
        
	quiz 3) 과목별 시작하지 않은 유저의 수
    	select c.course_id, c.title, count(*)
        	from courses c
        inner join enrolled e
        on c.course_id = e.course_id
        where e.is_registered = 0
        group by c.course_id
        
	quiz 4) 웹개발, 앱개발 종합반의 week별 체크인 수
    	select c1.title, c2.week, count(*) as cnt
        	from courses c1
        inner join checkins c2
        	on c1.course_id = c2.course_id
        group by c1.title, c2.week
        order by c1.title, c2.week

	quiz 5) q4에서 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.create_at = o.create_at
        where o.create_at >= '2020-08-01'
        group by c1.title, c2.week
        order by c1.title, c2.week

	ex) 7월10일~7월19일에 가입한 고객 중 
    	포인트를 가진 고객의 숫자, 전체 고객 숫자, 비율
	select count(pu.point_user_id),
   		   count(u.user_id),
           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'

		(
          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.create_at = o.create_at
          where o.create_at < '2020-08-01'
          group by c1.title, c2.week
          order by c1.title, c2.week
		)
        union all
        (
          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.create_at = o.create_at
          where o.create_at >= '2020-08-01'
          group by c1.title, c2.week
          order by c1.title, c2.week
       	)