SQLPad SQLPad Sign In

35. Film length report

easy

Instruction

  • Write a query to return the number of films in the following categories: short, medium, and long.
  • The order of your results doesn't matter.
Definition
  • short: less <60 minutes.
  • medium: >=60 minutes, but <100 minutes.
  • long: >=100 minutes

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 

 film_category | count
---------------+-------
 medium        |   1
 long          |   2
 short         |   3

Expected results

Solution postgres

SELECT
  CASE WHEN length < 60 THEN 'short'
  	   WHEN length < 100 THEN 'medium'
  	   WHEN length >= 100 THEN 'long'
  	   ELSE NULL
  	   END AS film_category,
  COUNT(*)
FROM film
GROUP BY film_category;

Explanation

This query is selecting data from a table called "film". The query is using a CASE statement to categorize films based on their length. Films with a length less than 60 minutes will be categorized as "short", films with a length between 60 and 100 minutes will be categorized as "medium", and films with a length greater than or equal to 100 minutes will be categorized as "long". The query then counts the number of films in each category and groups the results by the film category. The output will show the number of films in each category.


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%
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%
26 Second shortest film easy
29%
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%