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
    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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.