I am trying to write an SQL query that displays the course popularity, in descending order.
- Course popularity is measured in points, which determined as follows: For every survey:
- a. if the votes difference > 10% of total votes, the more popular course gets 1 point, and the less popular course gets 0 points
- b. if the votes difference <= 10% of total votes, each course gets 0.5 point
course_id | course_name | faculty |
---|---|---|
1001 | economics_101 | business |
1002 | algebra_101 | math |
1003 | geometry_101 | math |
1004 | management_101 | business |
1005 | marketing_101 | business |
1006 | physics_101 | science |
survey_id | option_a | option_b | votes_a | votes_b |
---|---|---|---|---|
2001 | economics_101 | geometry_101 | 61 | 34 |
2002 | algebra_101 | economics_101 | 31 | 68 |
2003 | marketing_101 | management_101 | 11 | 72 |
2005 | management_101 | algebra_101 | 43 | 54 |
2004 | geometry_101 | marketing_101 | 48 | 46 |
Result achieved so far:
course | popularity |
---|---|
economics_101 | 4 |
management_101 | 2 |
algebra_101 | 2 |
marketing_101 | 1 |
geometry_101 | 1 |
[NULL] | 0 |
I managed to join it so far, would be great to have inputs on optimizing this query:
WITH x AS
(
WITH b AS
(
WITH a as
(
select * from course c
LEFT JOIN survey s
on c.course_name = s.option_a
UNION ALL
select * from course c
LEFT JOIN survey s
on c.course_name = s.option_b
)
SELECT a.*,
SUM(votes_a votes_b) as total_votes,
CASE WHEN (a.votes_a - a.votes_b) > (0.1*SUM(votes_a votes_b)) THEN 1
WHEN (a.votes_b - a.votes_a) <= (0.1*SUM(votes_a votes_b)) THEN 0.5
ELSE 0
END AS 'Popularity_a',
CASE WHEN (a.votes_b - a.votes_a) > (0.1*SUM(votes_a votes_b)) THEN 1
WHEN (a.votes_a - a.votes_b) <= (0.1*SUM(votes_a votes_b)) THEN 0.5
ELSE 0
END AS 'Popularity_b'
FROM
a
GROUP BY
a.course_name ,
a.course_id,
a.faculty ,
a.survey_id ,
a.option_a ,
a.option_b ,
a.votes_a ,
a.votes_b
)
SELECT b.option_a as course,
b.Popularity_a as pop
FROM b
LEFT JOIN
course cx
ON b.option_a = cx.course_name
UNION ALL
SELECT b.option_b as course ,
b.Popularity_b as pop
FROM b
LEFT JOIN
course cx
ON b.option_b = cx.course_name
)
select
x.course ,
sum (x.pop) as popularity
from x
GROUP BY
x.course
order by popularity desc
CodePudding user response:
Use UNION ALL
to extract all courses and the respective points they get from the table survey
and aggregate to get the popularity
.
Then join to course
:
WITH
cte AS (
SELECT option_a course_name,
CASE
WHEN votes_a - votes_b > 0.1 * (votes_a votes_b) THEN 1.0
WHEN votes_b - votes_a > 0.1 * (votes_a votes_b) THEN 0.0
ELSE 0.5
END points
FROM survey
UNION ALL
SELECT option_b,
CASE
WHEN votes_b - votes_a > 0.1 * (votes_a votes_b) THEN 1.0
WHEN votes_a - votes_b > 0.1 * (votes_a votes_b) THEN 0.0
ELSE 0.5
END
FROM survey
),
points AS (
SELECT course_name, SUM(points) total_points
FROM cte
GROUP BY course_name
)
SELECT c.*, COALESCE(p.total_points, 0) popularity
FROM course c LEFT JOIN points p
ON p.course_name = c.course_name
ORDER BY popularity DESC;
See the demo.