53. Movie groups by rental income

hard

Instruction

  • Write a query to return the number of films in 3 separate groups: high, medium, low.
  • The order of your results doesn't matter.

Definition

  • high: revenue >= $100.
  • medium: revenue >= $20, <$100 .
  • low: revenue <$20.

Hint

  • If a movie has no rental revenue, it belongs to the low group

Table 1: 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 2: inventory

Each row is unique, inventoy_id is the primary key of this table.

   col_name   | col_type
--------------+--------------------------
 inventory_id | integer
 film_id      | smallint
 store_id     | smallint

Table 3: payment

Movie rental payment transactions table

   col_name   | col_type
--------------+--------------------------
 payment_id   | integer
 customer_id  | smallint
 staff_id     | smallint
 rental_id    | integer
 amount       | numeric
 payment_ts   | timestamp with time zone

Table 4: rental

   col_name   | col_type
--------------+--------------------------
 rental_id    | integer
 rental_ts    | timestamp with time zone
 inventory_id | integer
 customer_id  | smallint
 return_ts    | timestamp with time zone
 staff_id     | smallint

Sample results

 film_group | count
------------+-------
 medium     |   123
 high       |   456
 low        |   789

Expected results

Solution postgres

SELECT film_group, COUNT(*) 
FROM (
	SELECT 
		F.film_id, 
	    CASE WHEN SUM(P.amount) >= 100 THEN 'high'
	         WHEN SUM(P.amount) >= 20 THEN 'medium'
	         ELSE 'low' END film_group
	FROM film F
	LEFT JOIN inventory I
	ON I.film_id = F.film_id
	LEFT JOIN rental R 
	ON R.inventory_id = I.inventory_id
	LEFT JOIN payment P
	ON P.rental_id = R.rental_id
	GROUP BY F.film_id
) X
GROUP BY film_group
;
    

Explanation

This query is counting the number of films falling into three different categories based on their rental income. The categories are 'low', 'medium', and 'high'. The query first joins tables film, inventory, rental, and payment to obtain the rental income for each film. It then groups the films by their ID and assigns them to one of the three categories based on their rental income. Finally, the query counts the number of films in each category and returns the result.



More OUTER JOINS questions

ID Title Level FTPR
213 Interchange revenue by issuer and merchant category visa easy -
194 Daily active users report for new market snap easy -
191 Number of replies by group twitter medium
100%
172 Comments distribution social hard
13%
168 Average number of streams in the US tiktok medium
13%
140 Daily sales of restaurant 100011 doordash hard
9%
118 Daily bookings in the US airbnb hard
5%
100 Advertiser ROI advertising medium
9%
56 Total number of actors easy
32%
55 Busy days and slow days medium
11%
54 Customer groups by movie rental spend medium
21%
52 Movies cast by movie only actors easy
13%
51 Movie only actor easy
43%
45 Movie inventory optimization hard
7%
44 In-demand vs not-in-demand movies medium
6%
43 Customers who rented vs. those who did not medium
9%
42 Films that are in stock vs not in stock medium
11%
41 Productive actors vs less-productive actors hard
6%