Home > Blockchain >  SQL to find course popularity from a survey
SQL to find course popularity from a survey

Time:03-20

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.

  • Related