SQLPad
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 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.
Copied
Your results