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
Question 54
Customer groups by movie rental spend
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.
- high: movie rental spend
>= $150. - medium: movie rental spend
>= $100, <$150. - low: movie rental spend
<$100.
- If a customer spend 0 in movie rentals, he/she belongs to the
lowgroup.
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
Solution
postgresSELECT 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.
Copied
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Copied
Expected results
Submit a query to compare against expected output.
Postgres
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
10:00
Your results
Run your query to preview results here.