62. Shortest film by category

medium

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 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.



More ROW_NUMBER, RANK, DENSE_RANK questions

ID Title Level FTPR
208 Top 3 urls by testing groups apple hard
100%
203 Top product by country by month apple easy
100%
201 Top 3 students for each subject snap hard
100%
188 Top 1 popular question by department google hard
14%
166 Top 10 customers based on spend growth walmart medium
11%
165 Session stitching walmart hard
17%
158 First trip completion rate lyft medium
22%
157 Number of trips before a driver got banned lyft medium
10%
148 Most popular video category social medium
13%
145 Returning customers after first buy afterpay hard
16%
144 Third order afterpay medium
9%
142 First order date afterpay easy
40%
139 Poor first delivery experience doordash medium
17%
137 Extremely late first orders doordash medium
9%
135 Unlucky employees robinhood easy
16%
127 Average rating after 10th trip uber hard
11%
123 Top listing in the United States, United Kingdom and Canada airbnb medium
10%
122 Top country by wow growth airbnb hard
13%
120 First ever booking airbnb hard
10%
119 Top 2 countries by bookings airbnb hard
10%
116 Top answers day by device amazon easy
14%
110 Most popular product by category amazon medium
12%
109 Top 3 most popular product categories amazon easy
11%
105 Number of days gap between last two actions mobile hard
8%
90 Top artist report spotify medium
21%
88 Top song report spotify medium
22%
83 Top search_query in US and UK on new year's day search engine medium
6%
76 Districts with the most and least customers easy
12%
75 Top customer by movie category medium
26%
74 The most productive actors by category hard
7%
73 Number of days to become a happy customer hard
11%
72 Days when they became happy customers medium
10%
64 Top 2 films by category hard
14%
63 Top 5 customers by store hard
8%