Table 1: category
Movie categories.
col_name | col_type -------------+-------------------------- category_id | integer name | text
Question 62
Shortest film by category
Instruction
- Write a query to return the shortest movie from each category.
- The order of your results doesn't matter.
- If there are ties, return just one of them.
- Return the following columns:
film_id, title, length, category, row_num
Table 2: 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 3: film_category
A film can only belong to one category
col_name | col_type -------------+-------------------------- film_id | smallint category_id | smallint
Sample results
film_id | title | length | category | row_num
---------+---------------------+--------+-------------+---------
869 | SUSPECTS QUILLS | 47 | Action | 1
243 | DOORS PRESIDENT | 49 | Animation | 1
505 | LABYRINTH LEAGUE | 44 | Children | 1
Solution 1
postgresWITH movie_ranking AS (
SELECT
F.film_id,
F.title,
F.length,
C.name category,
ROW_NUMBER() OVER(PARTITION BY C.name ORDER BY F.length) row_num
FROM film F
INNER JOIN film_category FC
ON FC.film_id = F.film_id
INNER JOIN category C
ON C.category_id = FC.category_id
)
SELECT
film_id,
title,
length,
category,
row_num
FROM movie_ranking
WHERE row_num = 1
;Explanation
This query retrieves a list of movies with their category and length, ranked by length within each category. The query uses a common table expression (CTE) called "movie_ranking" to calculate the ranking using the ROW_NUMBER() window function. The CTE joins the film, film_category, and category tables to get the necessary data. The final SELECT statement retrieves the data from the CTE where the row number is equal to 1, which represents the shortest movie in each category.
Solution 2
postgresSELECT
film_id,
title,
length,
category,
row_num
FROM (
SELECT
F.film_id,
F.title,
F.length,
C.name category,
ROW_NUMBER() OVER(PARTITION BY C.name ORDER BY F.length) row_num
FROM film F
INNER JOIN film_category FC
ON FC.film_id = F.film_id
INNER JOIN category C
ON C.category_id = FC.category_id
) X
WHERE row_num = 1
;Explanation
This query is selecting data from a table called "film" and joining it with two other tables called "film_category" and "category". It is retrieving the film_id, title, length, and category name for each movie.
The query also uses the window function ROW_NUMBER() to assign a row number to each movie within its respective category, ordered by length.
The entire subquery is then given an alias "X", and the outer query filters the results to only display the first row within each category (WHERE row_num = 1).
In summary, this query is pulling data from multiple tables, ordering it by length within categories, and then selecting only the shortest movie in each category.
Copied
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Copied
Expected results
Submit a query to compare against expected output.
Postgres
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
Your results
Run your query to preview results here.