Instruction
- Write a query to return the title of the film with the second-largest cast.
Table 1: film
col_name | col_type
----------------------+--------------------------
film_id | integer
title | text
description | text
release_year | integer
language_id | smallint
original_language_id | smallint
rental_duration | smallint
rental_rate | numeric
length | smallint
replacement_cost | numeric
rating | text
Table 2: film_actor
Films and their casts
col_name | col_type -------------+-------------------------- actor_id | smallint film_id | smallint
Sample results
title ----------- SECOND LARGEST
Expected results
Solution postgres
WITH film_size AS (
SELECT film_id,
COUNT(*) AS actors_cnt
FROM film_actor
GROUP BY film_id
ORDER BY actors_cnt DESC
LIMIT 2
)
SELECT title
FROM film
WHERE film_id IN (
SELECT film_id
FROM film_size
ORDER BY actors_cnt
LIMIT 1
);
Explanation
This query is selecting the title of a movie with the smallest number of actors out of the two movies with the largest number of actors.
The first part of the query is creating a common table expression (CTE) called "film_size". This CTE is selecting the film_id and the count of actors in each film from the "film_actor" table. It then groups the results by the film_id and orders them by the count of actors in descending order. The "LIMIT 2" statement limits the results to only the top two movies with the most actors.
The second part of the query is selecting the title of the movie that has the smallest number of actors out of the two movies with the most actors. It does this by selecting the film_id from the "film_size" CTE and then selecting the title from the "film" table for the movie with the smallest number of actors. The "ORDER BY actors_cnt" and "LIMIT 1" statements ensure that only the movie with the smallest number of actors is returned.