***********
- ****************************************************************** > $20*********
- ************
********************************************************************************************************************* - ********************************************
Table: 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
year mon num_hp_customers 2020 2 158 2020 5 520
Solution postgres
SELECT
year,
mon,
COUNT(DISTINCT customer_id)
FROM (
SELECT
EXTRACT(YEAR FROM payment_ts) AS year,
EXTRACT(MONTH FROM payment_ts) AS mon,
customer_id,
SUM(amount) amt
FROM payment
GROUP BY year, mon, customer_id
) X
WHERE amt > 20
GROUP BY 1,2;
Explanation
This query is selecting data from a table called "payment." It is breaking down the data by year, month, and customer ID.
The query is first creating a subquery that calculates the year, month, customer ID, and the sum of the amount paid by each customer in each month.
The outer query then filters the subquery to only include customers who have paid more than $20 in a given month. It then groups the results by year and month, and counts the number of distinct customers who paid more than $20 in each month.
This query is useful for analyzing customer behavior and identifying trends in the amount paid by customers over time.
Expected results