SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE course (course_id INTEGER PRIMARY KEY, course_name VARCHAR(14), faculty VARCHAR(8));
✓
INSERT INTO course (course_id, course_name, faculty) VALUES
  ('1001', 'economics_101', 'business'),
  ('1002', 'algebra_101', 'math'),
  ('1003', 'geometry_101', 'math'),
  ('1004', 'management_101', 'business'),
  ('1005', 'marketing_101', 'business'),
  ('1006', 'physics_101', 'science');
✓
CREATE TABLE survey (survey_id INTEGER, option_a VARCHAR(14), option_b VARCHAR(14), votes_a INTEGER, votes_b INTEGER);
✓
INSERT INTO survey (survey_id, option_a, option_b, votes_a, votes_b) VALUES
  ('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');
✓
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;


course_id
course_name
faculty
popularity


1001
economics_101
business
2

1002
algebra_101
math
1

1004
management_101
business
1

1003
geometry_101
math
0.5

1005
marketing_101
business
0.5

1006
physics_101
science
0

course_id	course_name	faculty	popularity
1001	economics_101	business	2
1002	algebra_101	math	1
1004	management_101	business	1
1003	geometry_101	math	0.5
1005	marketing_101	business	0.5
1006	physics_101	science	0