[SQL] sub query, case, table

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

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

select pu.user_id, pu.point, 
	(case when pu.point > 10000 then '잘 하고 있어요!'	
	else '조금만 더 파이팅!'end) as msg
from point_users pu

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

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

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

---

select a.enrolled_id, a.done_cnt, b.total_cnt from ( 
	select enrolled_id , count(*) as done_cnt from enrolleds_detail ed 
	where ed.done = 1
	group by enrolled_id 
) a 
inner join ( 
	select enrolled_id , count(*) as total_cnt from enrolleds_detail ed
	group by enrolled_id 
) b on a.enrolled_id = b.enrolled_id

= (위와 아래의 결과는 같다) 

with table1 as (
	select enrolled_id , count(*) as done_cnt from enrolleds_detail 
	where done = 1
	group by enrolled_id 
),table2 as ( 
	select enrolled_id , count(*) as total_cnt from enrolleds_detail
	group by enrolled_id 
)
select a.enrolled_id, 
  	   a.done_cnt, 
  	   b.total_cnt 
  from table1 a 
inner join table2 b on a.enrolled_id = b.enrolled_id