SQL 从调查中查找课程受欢迎程度

我正在try 编写一个SQL查询来显示课程的受欢迎程度，

• 课程受欢迎程度以分数衡量，其确定如下:For every survey:分
• a、 如果票数不同&gt；10%的总票数，受欢迎程度较高的课程得1分，受欢迎程度较低的课程得0分
• b、 如果票数不同&lt；=总票数的10%，每门课程获得0.5票

迄今取得的成果:

到目前为止，我成功地加入了它，如果能为优化这个查询提供帮助，那就太好了:

    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