- ***********************************************************************************************************************************
*************************************.- ************************************************
- ****
****************************************************
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
customer_id | delta
-------------+-------
1 | 3.00
2 | 0.00
3 | 2.00
4 | -1.00
5 | -2.00
Solution postgres
WITH cust_spend_seq AS (
SELECT
customer_id,
payment_ts,
amount AS current_payment,
LAG(amount, 1) OVER(PARTITION BY customer_id ORDER BY payment_ts) AS prev_payment,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY payment_ts DESC) AS payment_idx
FROM payment P
)
SELECT
customer_id,
current_payment - prev_payment AS delta
FROM cust_spend_seq
WHERE customer_id IN(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
AND payment_idx = 1;
Explanation
This query calculates the difference in payment amounts (delta) between the current payment and the previous payment for each customer in a list of ten specified customer IDs. It does this by using a common table expression (CTE) called "cust_spend_seq" that selects the customer ID, payment timestamp, and amount for each payment in the "payment" table, and also calculates the previous payment amount using the LAG function and the payment index using the ROW_NUMBER function.
The main query then selects only the customer ID and delta columns from the CTE for the specified customer IDs and for the most recent payment (i.e. where the payment index is 1). This can be useful for analyzing customer spending patterns and identifying any significant changes in spending behavior between consecutive payments.
Expected results