Postgres 13

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE survey_logging
    (user_id int PRIMARY KEY, "action" varchar(10), question_id int)
    
;
   
INSERT INTO survey_logging
    (user_id, "action", question_id)
VALUES
    (1, 'answer', 1),
    (2, 'answer', 1),
    (3,'answer', 2),
    (4,'skip', 2),
    (5,'skip', 1),
    (6,'answer', 1),
    (7,'answer', 1),
    (8,'answer', 1),
    (9,'skip', 1),
    (10,'skip', 2)
;

10 rows affected

    SELECT 
        question_id,
        SUM (CASE
            WHEN action = 'answer' THEN 1 
            ELSE 0
            END) AS answered,
        COUNT(*) AS ovr_count
    FROM survey_logging
    GROUP BY question_id


question_id
answered
ovr_count


2
1
3

1
5
7


WITH response_by_q AS (
    SELECT 
        question_id,
        SUM (CASE
            WHEN action = 'answer' THEN 1 
            ELSE 0
            END) AS answered,
        COUNT(*) AS ovr_count
    FROM survey_logging
    GROUP BY question_id
),
extra_column AS (
    SELECT 
        question_id,
        ROUND(answered * 100.0 / ovr_count, 1) AS answer
  	FROM response_by_q
)
SELECT question_id
FROM extra_column
WHERE answer = (SELECT MAX(answer) FROM extra_column)


question_id


1