By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE survey_log (
uid int,
action varchar(255),
question_id int,
answer_id int,
q_num int,
timestamp int);
INSERT INTO survey_log VALUES
('5', 'show', '285', null, '1', '123'),
('5', 'answer', '285', '124124', '1', '124'),
('5', 'show', '369', null, '2', '125'),
('5', 'skip', '369', null, '2', '126');
WITH Answers AS (
SELECT *,
COUNT(CASE action WHEN 'show' THEN action END) OVER (PARTITION BY question_id ORDER BY question_id) AS show_count,
COUNT(answer_id) OVER (PARTITION BY question_id ORDER BY question_id) AS answer_count
FROM survey_log
),
A AS (
SELECT question_id,
MAX(answer_count)/MAX(show_count) AS ranked
FROM Answers
GROUP BY question_id
)
SELECT question_id
FROM A
WHERE ranked = (SELECT MAX(ranked) FROM A)
;
| question_id |
|---|
| 285 |