Mysql – Group rows by running aggregate

aggregateMySQLrunning-totals

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

Best Answer

One option is to leave some of this functionality to the application code. It's possible to query for the relevant data with

select * from transactions t
    where exists(
        select 1 from transactions t2
            where t2.coupon = 'bCoupon'
                and t.user_id = t2.user_id
                and t2.date_paid > t.date_paid
    )
    order by date_paid

And then fold over that.

Related Question