스파르타 SQL 3주차

left join

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

inner join(교집합)

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

checkins 테이블에 courses 테이블 연결해 통계치 내보기

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

point users 테이블과 users 테이블을 연결해 순서대로 정렬

select * from point_users pu 
inner join users u on pu.user_id =u.user_id 
order by pu.point desc

주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저중 성씨별 주문개수 세기

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

결제수단별 유저 포인트의 평균값

select o.payment_method, round(avg(pu.point),0) from point_users pu 
inner join orders o on pu.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 e.is_registered ="0"
group by u.name 
order by cnt_name desc

웹개발, 앱개발 종합반의 week 별 체크인 수

SELECT c.title, c2.week, count(*) from courses c 
inner join checkins c2 on c.course_id = c2.course_id 
group by c.title, c2.WEEK 
order by title, c2.week

위 문제에서 8월 1일 이후에 구매한 고객들만

SELECT c.title, c2.week, count(*) from courses c 
inner join checkins c2 on c.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 c.title, c2.week 
order by c.title, c2.week 

NULL

select u.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 u.name

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

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

Union

(
	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
/*	order by c1.title, c2.week*/
)
UNION 
(
	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
/*	order by c1.title, c2.week*/
)