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 movie (
id int PRIMARY KEY
, yr int
);
CREATE TABLE actor (
id int PRIMARY KEY
, name text NOT NULL -- assuming name unique?
);
CREATE TABLE casting (
movieid int REFERENCES movie
, actorid int REFERENCES actor
, PRIMARY KEY (movieid, actorid)
);
INSERT INTO actor VALUES
(1, 'Buster Keaton')
, (2, 'John Travolta')
, (3, 'Harvey Keitel')
;
INSERT INTO movie VALUES
( 1, '1976')
, ( 2, '1976')
, ( 3, '1977')
, ( 4, '1977')
, ( 5, '1978')
, ( 6, '1978')
, ( 7, '1981')
, ( 8, '1981')
, ( 9, '1994')
, (10, '1994')
, (11, '1989')
, (12, '1999')
;
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 12
INSERT 0 14
-- Yes, you can do a `max(count(*))` in SQL
SELECT m.yr, count(*) AS movie_count
, max(count(*)) OVER () AS max_ct
FROM casting c
JOIN movie m ON c.movieid = m.id
WHERE c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
GROUP BY m.yr
ORDER BY count(*) DESC;
| yr | movie_count | max_ct |
|---|---|---|
| 1976 | 2 | 2 |
| 1977 | 2 | 2 |
| 1978 | 2 | 2 |
| 1981 | 2 | 2 |
| 1994 | 2 | 2 |
| 1989 | 1 | 2 |
SELECT 6