35. Film length report

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

film_id	title	description	release_year	language_id	original_language_id	rental_duration	rental_rate	length	replacement_cost	rating	last_update
1	ACADEMY DINOSAUR	A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies	2006	1	NULL	6	0.99	86	20.99	PG	2017-09-10 17:46:03.905795-07
2	ACE GOLDFINGER	A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China	2006	1	NULL	3	4.99	48	12.99	G	2017-09-10 17:46:03.905795-07

       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

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.

Expected results

Your results

More IN, BETWEEN, LIKE, CASE WHEN questions

ID	Title	Level	FTPR
178	Members who worked at both Microsoft and Google linkedin	medium	34%
177	Purchases by platform report amazon	medium	10%
176	Employees' annual bonus amazon	easy	12%
160	Sellers with no sales by day ebay	hard	11%
156	Cancellation rate by unbanned users lyft	hard	15%
155	Driver with the highest cancellation rate lyft	easy	24%
151	Salary report dropbox	easy	18%
138	Happy restaurants doordash	easy	14%
136	Extremely late orders doordash	easy	11%
131	Churned accounts affirm	hard	10%
102	Histogram by visit session duration google	hard	17%
34	Stocked up movies	easy	27%
33	Returning customers	medium	8%
32	Unpopular movies	hard	17%
31	Movies that have not been returned	easy	26%
30	Inactive customers in May	easy	22%
29	Second highest spend customer	medium	14%
28	Film with the second largest cast	medium	26%
27	Film with the largest cast	easy	27%
26	Second shortest film	easy	30%
25	Shortest film	easy	46%
24	Films with more than 10 actors	medium	19%
22	Average cost per rental transaction	easy	42%
15	Fast movie watchers vs slow watchers	hard	6%
14	Good days and bad days	hard	4%
13	Actors' first name	medium	14%
12	Actors' last name ending in 'EN' or 'RY'	easy	27%
11	Actors' last name	easy	21%