2020. 12. 09 수요일

새로운 테이블에 대해 알아보았다.

• UDEMY Course 테이블에는 UDEMY의 강의에 대한 데이터가 담겨있다.

• 코스는 크게 4가지로 나뉜다.

  • 웹 개발

  • 비지니스 금융

  • 그래픽 디자인

  • 악기

• 강의의 제목, 수강생 수, 수강료 유무, 수강비, 등록일자, 레벨(난이도), 코스 등의 데이터가 담겨있다.

지표를 선정하였다.

• 이번 분석에서 도출해야할 결론은 다음과 같다.

  • 가장 인기있는 코스

    • 수강생 수

    • 총 매출

  • 차기 콘텐츠 선정

    • 코스

    • 수강료 유무

    • 레벨

• 선정한 지표는 다음과 같다.

  • 코스별 수강생 수

  • 코스별 총 매출

  • 코스별 유/무료 강의 비율

  • 코스별 유료 강의 수강생 비율

  • 난이도별 강의 비율

  • 난이도별 수강생 비율

지표를 구하기 위한 Query를 작성하였다.

총매출을 제외한 지표는 퍼센티지도 구하도록 했다.

• 코스별 총 매출

SELECT
	subject,
	SUM(num_subscribers * price) AS revenue
FROM 
	`untechbox-sql.udemy.course_updated`
GROUP BY 
	subject
;

• 코스별 수강생, 강의 수

WITH sub AS
	(
	SELECT
		subject,
		SUM(num_subscribers) AS num_sub,
		COUNT(*) AS num_course
	FROM 
		`untechbox-sql.udemy.course_updated`
	GROUP BY
		subject
	),
sub2 AS
	(
	SELECT
		SUM(num_subscribers) AS total_sub,
		COUNT(*) AS total_course
	FROM 
		`untechbox-sql.udemy.course_updated`
	)
SELECT
	sub.subject,
	num_sub,
	ROUND(num_sub / total_sub * 100.0, 2) AS perc_sub,
	num_course,
	ROUND(num_course / total_course * 100.0, 2) AS perc_course
FROM 
	sub,
	sub2
ORDER BY 
	subject
;

• 코스, 유/무료별 수강생, 강의 수

WITH sub AS
	(
	SELECT
		subject,
		is_paid,
		SUM(num_subscribers) AS num_sub,
		COUNT(*) AS num_course
	FROM 
		`untechbox-sql.udemy.course_updated`
	GROUP BY
		subject,
		is_paid
	),
sub2 AS
	(
	SELECT
		subject,
		SUM(num_subscribers) AS total_sub,
		COUNT(*) AS total_course
	FROM 
		`untechbox-sql.udemy.course_updated`
	GROUP BY
		subject
	)
SELECT
	sub.subject,
	sub.is_paid,
	num_sub,
	ROUND(num_sub / total_sub * 100.0, 2) AS perc_sub,
	num_course,
	ROUND(num_course / total_course * 100.0, 2) AS perc_course
FROM 
	sub,
	sub2
WHERE
	sub.subject = sub2.subject
ORDER BY 
	subject
;

• 코스, 난이도별 수강생, 강의 수

WITH sub1 AS
	(
	SELECT
		subject,
		COUNT(*) AS course,
		SUM(num_subscribers) AS subscr
	FROM
		`untechbox-sql.udemy.course_updated`
	GROUP BY
		subject
	),
sub2 AS 
	(
	SELECT
		subject,
		level,
		COUNT(*) AS num_course,
		SUM(num_subscribers) AS num_sub,
	FROM
		`untechbox-sql.udemy.course_updated`
	GROUP BY 
		subject,
		level
	)
SELECT
	sub1.subject,
	sub2.level,
	num_course,
	ROUND(num_course / course * 100.0, 2) AS perc_lev_course,
	num_sub,
	ROUND(num_sub / subscr * 100.0, 2) AS perc_lev_sub
FROM  
	sub1, 
	sub2
WHERE 
	sub1.subject = sub2.subject
ORDER BY 
	subject
;