28. Film with the second largest cast

medium

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.



More IN, BETWEEN, LIKE, CASE WHEN questions

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