SQL study_5

[퀴즈 1] 결제 수단 별 유저 포인트의 평균값 구해보기 (어느 결제수단이 가장 열심히 듣고 있나~)

 

내가 쓴 오답

SELECT pu.point, AVG(*) FROM point_users pu

inner join orders o on pu.user_id = o.user_id

WHERE u.point

GROUP by o.payment_method

 정답

SELECT o.payment_method, ROUND(AVG(pu.`point`),2) FROM point_users pu

inner join orders o on pu.user_id = o.user_id

GROUP by o.payment_method

 

[퀴즈 2] 결제하고 시작하지 않은 유저들을 성씨별로 세어보기 (어느 성이 가장 시작을 안하였는가~)

 

SELECT name, COUNT(*) as cnt FROM enrolleds e

inner join users u on e.user_id = u.user_id

WHERE is_registered LIKE '0'

GROUP by name

order by COUNT(*) DESC

 

[퀴즈 3] 과목 별로 시작하지 않은 유저들을 세어보기

 

내가 쓴 오답

SELECT course_id, COUNT(title) FROM courses c

left join enrolleds e on c.course_id = e.course_id

GROUP by title LIKE e.is_registered = 0

 

정답

SELECT c.course_id, c.title, COUNT(*) as cnt_nostart FROM courses c

inner join enrolleds e on c.course_id = e.course_id

WHERE e.is_registered = 0

group by c.course_id

 

[퀴즈 4] 웹개발, 앱개발 종합반의 week 별 체크인 수

 

내가 쓴 오답

SELECT title, week, COUNT(*) FROM courses c1

inner join checkins c2 on c1.course_id =c2.course_id

GROUP by title , WEEK(*)

ORDER by title , WEEK(*) DESC

 

정답

SELECT c1.title, c2.week, COUNT(*) 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

 

 

[퀴즈 4] 연습4번에서, 8월 1일 이후에 구매한 고객들만 골라내기

 

내가 쓴 오답

SELECT * 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-02

정답

SELECT c1.title, c2.week, COUNT(*) 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

------------------------------------------

배운 코드가 쌓이다 보니까 마구잡이로 헷갈리기 시작함.