Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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') > ; > > INSERT INTO casting(actorid, movieid) VALUES > (1, 1) > , (1, 12) > , (2, 1) > , (2, 2) > , (2, 3) > , (2, 4) > , (2, 5) > , (2, 6) > , (2, 7) > , (2, 8) > , (2, 9) > , (2, 10) > , (2, 11) > , (3, 11) > ; > > <pre> > ✓ > > ✓ > > ✓ > 3 rows affected > 12 rows affected > 14 rows affected > </pre> <!-- --> > -- Postgres 13: WITH TIES > SELECT m.yr, count(*) AS movie_count > 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 -- can't sort by year for this > FETCH FIRST 1 ROWS WITH TIES; > > <pre> > yr | movie_count > ---: | ----------: > 1976 | 2 > 1977 | 2 > 1978 | 2 > 1981 | 2 > 1994 | 2 > </pre> <!-- --> > -- Older versions: with rank() in subquery > SELECT yr, movie_count > FROM ( > SELECT m.yr, count(*) AS movie_count > , rank() OVER (ORDER BY count(*) DESC) AS rnk > 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 > ) sub > WHERE rnk = 1 > ORDER BY yr; -- optionally sort by year > > <pre> > yr | movie_count > ---: | ----------: > 1976 | 2 > 1977 | 2 > 1978 | 2 > 1981 | 2 > 1994 | 2 > </pre> <!-- --> > -- There would be more year, but with fewer movies: > SELECT m.yr, count(*) AS movie_count > 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; > > <pre> > yr | movie_count > ---: | ----------: > 1976 | 2 > 1977 | 2 > 1978 | 2 > 1981 | 2 > 1994 | 2 > 1989 | 1 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=dae6869a2252393c0c0d0d5cdbc73832)*
back to fiddle