53. Movie groups by rental income

hard

***********

  • ************************************************************************************
  • *****************************************

**********

  • **************>= $100.
  • ****************>= $20, <$100 .
  • *************<$20.

****

  • *************************************************************

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

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.

Expected results



More OUTER JOIN questions

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