SQLPad
Instruction
- Write a query to return the title of the film with the largest cast (most actors).
- If there are ties, return just one of them.
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
------------------
LARGEST MOVIE
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 1
)
SELECT title
FROM film
WHERE film_id IN (
SELECT film_id
FROM film_size
);
Explanation
This query retrieves the title of the movie that has the most actors in it.
First, it creates a temporary table called "film_size" using a subquery that counts the number of actors in each movie and orders them by the count in descending order. It then selects the movie with the highest actor count using the LIMIT 1 clause.
Finally, it selects the title of the movie from the "film" table that matches the movie with the highest actor count using a WHERE clause that filters by the film_id column in the "film_size" table.
Copied
Your results