26. Second shortest film

easy

Instruction

  • Write a query to return the title of the second shortest film based on its duration/length.
  • A movie's duration can be found using the length column.
  • If there are ties, return just one of them.

Table: 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

Sample results

    title
--------------
 SECOND SHORTEST

Expected results

Solution 1: postgres

SELECT  title     
FROM film    
ORDER BY length     
LIMIT 1 
OFFSET 1;
    

Explanation

This query is asking the database to retrieve the title of a movie from the film table. The results are ordered by the length of the movie in ascending order. The LIMIT 1 clause means that only one movie title will be returned. The OFFSET 1 clause means that the first movie in the result set will be skipped, and the second movie title will be returned.

Solution 2: postgres

-- WINDOW FUNCTION BASED SOLUTION
WITH film_length_ranking AS (
	SELECT title, ROW_NUMBER() OVER (ORDER BY length) ranking
	FROM film
)
SELECT title
FROM film_length_ranking
WHERE ranking = 2;
    

Explanation

This query is using a window function to rank the films by length and then selecting the title of the film that has a ranking of 2.

First, the query creates a Common Table Expression (CTE) called "film_length_ranking". This CTE selects the film title and assigns a ranking to each film based on its length. The "ROW_NUMBER()" function is used with the "OVER (ORDER BY length)" clause to assign a ranking to each film based on its length, with the shortest film being ranked 1.

Next, the query selects the title of the film that has a ranking of 2. The "WHERE" clause is used to filter the results to only show the film with a ranking of 2.

Overall, this query is selecting the title of the film that is ranked second in terms of length.

Solution 3: postgres

-- FOR THOSE FOLLOWING THE COURSE
WITH shortest_2 AS (
    SELECT film_id, length
    FROM film
    ORDER BY length
    LIMIT 2
)
SELECT title 
FROM film
WHERE film_id IN (
    SELECT film_id
    FROM shortest_2
    ORDER BY length DESC
    LIMIT 1   
);
    

Explanation

This query is selecting the title of the film with the second shortest length. It first creates a temporary table called "shortest_2" that selects the film_id and length from the "film" table and orders them by length in ascending order. It then selects the top 2 rows from this table by using the "LIMIT 2" clause.

The outer query then selects the title from the "film" table where the film_id is in the temporary table "shortest_2", but only selects the film with the longest length of the two shortest films by ordering the temporary table by length in descending order and selecting the top row with "LIMIT 1".

Overall, this query is finding the title of the film that is second shortest in length, but is longer than the shortest film in the "film" table.



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%
28 Film with the second largest cast medium
25%
27 Film with the largest cast easy
27%
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%