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 polls (id INTEGER, title TEXT, owner_username TEXT);
CREATE TABLE options (id INTEGER PRIMARY KEY, option_text TEXT, poll_id INTEGER);
CREATE TABLE votes (username TEXT, option_id INTEGER, FOREIGN KEY(option_id) REFERENCES options(id));
INSERT INTO polls VALUES (1, 'Flask vs. Django', 'jose');
INSERT INTO polls VALUES (2, 'Python vs. Java', 'rolf');
INSERT INTO options VALUES (1, 'Flask', 1);
INSERT INTO options VALUES (2, 'Django', 1);
INSERT INTO options VALUES (3, 'It Depends', 1);
INSERT INTO options VALUES (4, 'Python', 2);
INSERT INTO options VALUES (5, 'Java', 2);
INSERT INTO votes VALUES ('jose', 1);
INSERT INTO votes VALUES ('jose', 4);
INSERT INTO votes VALUES ('rolf', 1);
INSERT INTO votes VALUES ('anne', 1);
INSERT INTO votes VALUES ('bob', 4);
INSERT INTO votes VALUES ('rolf', 5);
INSERT INTO votes VALUES ('charlie', 2);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
SELECT
polls.title,
options.option_text,
COUNT(votes) AS vote_count,
RANK() OVER(PARTITION BY polls.title ORDER BY COUNT(votes) DESC)
FROM polls
LEFT JOIN options ON options.poll_id = polls.id
LEFT JOIN votes ON votes.option_id = options.id
GROUP BY polls.title, options.option_text;
| title | option_text | vote_count | rank |
|---|---|---|---|
| Flask vs. Django | Flask | 3 | 1 |
| Flask vs. Django | Django | 1 | 2 |
| Flask vs. Django | It Depends | 0 | 3 |
| Python vs. Java | Python | 2 | 1 |
| Python vs. Java | Java | 1 | 2 |