Question 24
>= 10 actors.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 ------------------------ ACADEMY DINOSAUR ARABIA DOGMA
Solution
postgresWITH film_casts_cnt AS (
SELECT
film_id,
COUNT(*) AS actors_cnt
FROM film_actor
GROUP BY film_id
HAVING COUNT(*)>=10
)
SELECT title
FROM film
WHERE film_id IN (
SELECT film_id
FROM film_casts_cnt
)Explanation
This query is selecting the titles of all films that have 10 or more actors in their cast.
The first part of the query creates a temporary table called "film_casts_cnt" which contains the count of actors in each film. It does this by grouping the "film_actor" table by film_id and counting the number of rows in each group. It then filters this table to only include films with 10 or more actors.
The second part of the query selects the titles of all films where the film_id is in the "film_casts_cnt" table. This effectively joins the two tables on the film_id column and returns the title for each matching row.
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Submit a query to compare against expected output.
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
10:00
Run your query to preview results here.