스파르타코딩클럽
[왕초보] 엑셀보다 쉬운, 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
)