I have a set of transactions that can either be payments or purchases. The purchases can be of different types (e.g. 'ApplePurchase', 'OrangePurchase').
I will select some payments by their coupon code. For each payment, I wish to discard all the purchases up to the last time, prior to that payment, that the balance reached zero. I then wish to aggregate the remaining purchases. (Thanks to @Andriy M for that phrasing and apologies for butchering it).
Any advice on how to approach writing (part of) the solution in SQL or would be much appreciated.
Here is some example data:
create table users (id integer primary key); create table transactions (user_id integer references users (id), type enum('ApplePurchase', 'OrangePurchase', 'Payment'), coupon varchar(10), date_paid date, amount integer); insert into users (id) values (1), (2), (3), (4), (5); insert into transactions (user_id, type, coupon, date_paid, amount) values (1, 'ApplePurchase', NULL, '2003-01-02', 5), (2, 'ApplePurchase', NULL, '2003-01-02', 15), (1, 'Payment', 'aCoupon', '2003-01-02', 5), # here user 1's balance reaches 0 (2, 'OrangePurchase', NULL, '2003-01-03', 10), (2, 'Payment', 'bCoupon', '2003-01-03', 23), (1, 'ApplePurchase', NULL, '2003-01-05', 15), (1, 'Payment', 'bCoupon', '2003-01-05', 15), (1, 'ApplePurchase', NULL, '2003-01-07', 15), (1, 'Payment', 'bCoupon', '2003-01-07', 15);
Perhaps I am looking to perform this aggregation for payments of coupon 'bCoupon'. Member 2 has one payment of that type and 3 purchases before it. As there are no intervening payments, we only need to aggregate the purchases (as in the first row). Member two has two payments of that type. The first has two purchases before it, but the very first purchase is cancelled by an earlier payment. In that case, I am looking for a result of the form
Date | Member | Apple | Orange | Payment | Balance 2003-03-01 | 2 | 15 | 10 | 23 | 2 2003-05-01 | 1 | 15 | 0 | 15 | 0 2003-07-01 | 1 | 15 | 0 | 15 | 0