- 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 1: category
Movie categories.
col_name | col_type -------------+-------------------------- category_id | integer name | text
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
Expected results
Solution 1: postgres
WITH 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: postgres
SELECT
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.