27. Film with the largest cast

easy

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

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.

Expected results



More IN, BETWEEN, LIKE, CASE WHEN questions

ID Title Level FTPR
178 Members who worked at both Microsoft and Google linkedin medium
34%
177 Purchases by platform report amazon medium
10%
176 Employees' annual bonus amazon easy
12%
160 Sellers with no sales by day ebay hard
11%
156 Cancellation rate by unbanned users lyft hard
15%
155 Driver with the highest cancellation rate lyft easy
24%
151 Salary report dropbox easy
18%
138 Happy restaurants doordash easy
14%
136 Extremely late orders doordash easy
11%
131 Churned accounts affirm hard
10%
102 Histogram by visit session duration google hard
17%
35 Film length report easy
27%
34 Stocked up movies easy
27%
33 Returning customers medium
8%
32 Unpopular movies hard
17%
31 Movies that have not been returned easy
26%
30 Inactive customers in May easy
22%
29 Second highest spend customer medium
14%
28 Film with the second largest cast medium
26%
26 Second shortest film easy
30%
25 Shortest film easy
46%
24 Films with more than 10 actors medium
19%
22 Average cost per rental transaction easy
42%
15 Fast movie watchers vs slow watchers hard
6%
14 Good days and bad days hard
4%
13 Actors' first name medium
14%
12 Actors' last name ending in 'EN' or 'RY' easy
27%
11 Actors' last name easy
21%