SQL to find course popularity from a survey-CodePudding

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.