스파르타코딩클럽, [왕초보] 엑셀보다 쉬운, SQL 4주차 개발일지

select u.user_id, u.name, u.email from users u
inner join orders o on u.user_id = o.user_id 
where o.payment_method = 'kakaopay'

select user_id, name, email from users u 
where user_id in (
	select user_id from orders o 
	where payment_method = 'kakaopay'
)

select user_id, name, email from users u 
where user_id in (
	select user_id from orders o 
	where payment_method = 'kakaopay'
)

• select할 때마다 subquery가 실행됨

  select c.checkin_id, 
  		   c.user_id, 
  		   c.likes,
  		   (
  		    select avg(likes) from checkins
  		   	where user_id = c.user_id
  		   ) as avg_likes_user
  	from checkins c

select pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
	select user_id, round(avg(likes), 1) as avg_likes from checkins c 
	group by user_id
) a on pu.user_id = a.user_id

1. 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

select * from point_users pu
where pu.point > (
	select avg(point) from point_users
)

2. 이씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가진 유저들의 데이터 추출하기

select * from point_users pu2
where pu2.point > (
	select avg(pu.point) from users u
	inner join point_users pu on u.user_id = pu.user_id 
	where u.name = '이**'
)

select * from point_users pu2
where pu2.point > (
	select avg(pu.point) from point_users pu
	where user_id in (
		select user_id from users u 
		where name = '이**'
	)
)

3. checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙이기

select c.checkin_id,
	   c.course_id,
	   c.user_id,
	   c.likes,
	   (
	    select round(avg(likes), 1) from checkins c2
	    where c.course_id = c2.course_id 
		group by c2.course_id 
	   ) as course_avg
from checkins c

4. checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙이기

select c.checkin_id, c2.title, c.user_id, c.likes,
	   (
		select round(avg(likes), 1) from checkins
		where course_id = c.course_id 
		group by course_id 	   	
	   ) as course_avg
from checkins c
inner join courses c2 on c.course_id = c2.course_id 
order by course_avg

1. course_id별 체크인수, 인원, 비율 구하기

select a.course_title ,
	   count(distinct(c.user_id)) as cnt_checkins, 
	   a.cnt_total,
	   count(distinct(c.user_id)) / a.cnt_total as ratio
from checkins c
inner join (
	select course_id, course_title, count(*) as cnt_total from orders
	group by course_id 
) a on a.course_id = c.course_id
group by c.course_id

select b.course_title, a.cnt_checkins, b.cnt_total,
	   a.cnt_checkins / b.cnt_total as ratio
from (
	select course_id, count(distinct(user_id)) as cnt_checkins
	from checkins 
	group by course_id 
) a 
inner join (
	select course_id, course_title, count(*) as cnt_total
	from orders
	group by course_id 
) b on a.course_id = b.course_id

with table1 as (	
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
), table2 as (
	select course_id, count(*) as cnt_total from orders
	group by course_id 
)

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

select user_id, email, SUBSTRING_INDEX(email, '@', 1)
from users

select SUBSTRING(created_at, 1, 10) as date, count(*)
from orders
group by date

with table1 as (
	select pu.user_id, pu.point,
		   (case when pu.point > 10000 then '1만 이상'
		         when pu.point > 5000 then '5천 이상'
		   else '5천 미만' end) as lv
	from point_users pu
)

select a.lv, count(*) from table1 a 
group by a.lv

1. 평균 이상 포인트를 가지고 있으면 '잘하고 있어요' / 낮으면 '열심히 합시다' 표시

select point_user_id, point,
	   (case when point > ( select avg(point) from point_users pu )
	   	          then '잘하고 있어요'
	         else '열심히 합시다' end) as msg	 
from point_users pu 

2. 이메일 도메인별 유저의 수 세어보기

select SUBSTRING_INDEX(email, '@', -1) as domain, 
       count(*) as cnt_domain 
from users u
group by domain 

select domain, count(*) as cnt from (
	select SUBSTRING_INDEX(email, '@', -1) as domain from users u
) a
group by domain

3. '화이팅'이 포함된 오늘의 다짐만 출력해보기

select comment from checkins c
where comment like '%화이팅%'

4. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력하기

select a.enrolled_id, 
       b.cnt_done, 
       a.cnt_total,
       b.cnt_done / a.cnt_total as ratio
from (
	select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
	group by enrolled_id 
) a
inner join (
	select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
	where done = 1
	group by enrolled_id 
) b on a.enrolled_id =  b.enrolled_id 
order by ratio desc

select enrolled_id, 
       sum(done) as cnt_done,
       count(*) as cnt_total,
       sum(done) / count(*) as ratio
from enrolleds_detail ed 
group by enrolled_id 
order by ratio desc