54. Customer groups by movie rental spend

medium

Instruction
  • Write a query to return the number of customers in 3 separate groups: high, medium, low.
  • The order of your results doesn't matter.
Definition
  • high: movie rental spend >= $150.
  • medium: movie rental spend >= $100, <$150.
  • low: movie rental spend <$100.
Hint
  • If a customer spend 0 in movie rentals, he/she belongs to the low group.

Table 1: customer

  col_name   | col_type
-------------+--------------------------
 customer_id | integer
 store_id    | smallint
 first_name  | text
 last_name   | text
 email       | text
 address_id  | smallint
 activebool  | boolean
 create_date | date
 active      | integer

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

Sample results

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

Expected results

Solution postgres

SELECT customer_group, COUNT(*) 
FROM (
	SELECT 
		C.customer_id,
	    CASE WHEN SUM(P.amount) >= 150 THEN 'high'
	         WHEN SUM(P.amount) >= 100 THEN 'medium'
	         ELSE 'low' END customer_group
	FROM customer C
	LEFT JOIN payment P
	ON P.customer_id = C.customer_id
	GROUP BY C.customer_id
) X
GROUP BY customer_group
;
    

Explanation

This query is used to classify customers based on their spending amount and count the number of customers in each group. The query first joins the customer and payment tables based on the customer_id column. It then groups the data by customer_id and calculates the total amount spent by each customer. Based on the total amount spent, the customers are classified into three groups - high, medium, and low. Finally, the query counts the number of customers in each group and displays 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
20%
191 Number of replies by group twitter medium
18%
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
8%
118 Daily bookings in the US airbnb hard
5%
100 Advertiser ROI social medium
8%
56 Total number of actors easy
30%
55 Busy days and slow days medium
10%
53 Movie groups by rental income hard
11%
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%