Skip to content

Instantly share code, notes, and snippets.

@heyitsjames

heyitsjames/subcategory_course_quality_rating.sql

Created November 11, 2014 01:41
Show Gist options
  • Save heyitsjames/8aa4d33bb2642df5d71d to your computer and use it in GitHub Desktop.
Save heyitsjames/8aa4d33bb2642df5d71d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
subcategory_course_quality_rating.sql
-- Get the average course rating for a specific subcategory
SELECT sub_id, AVG(3.15+predicted_nps*1.00/50) AS avg_new_score
FROM
(SELECT con.courseId, c.title, u.title AS name, chs.courseSubcategoryId as sub_id,
CASE
WHEN nps.size>40
THEN nps.nps
WHEN nps.size<10
OR nps.size IS NULL
AND rat.size<5
OR rat.size IS NULL
THEN (con.nps*con.size/2+nps_subcategory.nps)/(con.size*1.00/2+1)
WHEN nps.size<10 OR nps.size IS NULL
AND rat.size>10
THEN rat.nps
WHEN nps.size<10
OR nps.size IS NULL
THEN (rat.size*rat.nps + LEAST(10-rat.size,con.size)*con.nps)/(rat.size+LEAST(10-rat.size,con.size))
WHEN rat.size<5
THEN (nps.size*nps.nps + LEAST(40-nps.size,con.size)*con.nps)/(nps.size+LEAST(40-nps.size,con.size))
WHEN rat.size>10
THEN (nps.size*nps.nps + LEAST(40-nps.size,rat.size)*rat.nps)/(nps.size+LEAST(40-nps.size,rat.size))
ELSE
(nps.size*nps.nps + (40-nps.size)*rat.nps + LEAST(40-nps.size-rat.size,con.size,0)*con.nps) / (40+LEAST(40-nps.size-rat.size,con.size,0))
END AS predicted_nps
FROM (SELECT courseId, 10+10*LN(CAST(COUNT(*)*.25/COUNT(DISTINCT userId) AS REAL)) AS nps, COUNT(DISTINCT userId) AS size
FROM user_progressed_lecture
WHERE total >1
AND created > current_date - interval '3 month'
GROUP BY courseId
HAVING MIN(created) < current_date - interval '1 week') con
INNER JOIN course c
ON c.id = con.courseId
AND c.created > current_date - INTERVAL '2 year'
AND c.isPublished='Yes'
AND c.isPrivate='No'
INNER JOIN u_user u
ON c.userId = u.id
LEFT JOIN course_has_subcategory chs
ON c.id = chs.courseId
AND chs.courseSubcategoryId = 24
INNER JOIN
(SELECT chs.courseSubcategoryId, 100*SUM(
CASE
WHEN ucf.score >=9
THEN 1.00
WHEN ucf.score <=6
THEN -1.00 END)/COUNT(ucf.score)
AS nps
FROM user_course_feedback ucf, course_has_subcategory chs
WHERE ucf.courseId=chs.courseId
AND ucf.created > current_date - interval '3 month'
GROUP BY 1) nps_subcategory
ON chs.courseSubcategoryId = nps_subcategory.courseSubcategoryId
LEFT JOIN (SELECT courseId, CASE WHEN COUNT(rating)>=5 THEN -180+50*AVG(rating*1.00) END AS nps, COUNT(rating) AS size
FROM course_review
WHERE isSpam =0
AND created > current_date - interval '3 month'
GROUP BY courseId) AS rat
ON con.courseId = rat.courseId
LEFT JOIN (SELECT courseId, 100*SUM(CASE WHEN score >=9 THEN 1.00 WHEN score <=6 THEN -1.00 END)/COUNT(score) AS nps, COUNT(score) AS size
FROM user_course_feedback
WHERE created > current_date - interval '3 month'
GROUP BY courseId) nps
ON nps.courseId = con.courseId
ORDER BY 1 DESC)
GROUP BY sub_id
;
--- END QUERY --
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment