Finding date where conditions within 30 days has elapsed

Question

For my website, I have a loyalty program where a customer gets some goodies if they've spent $100 within the last 30 days. A query like below:

SELECT u.username, SUM(total-shipcost) as tot
    FROM orders o
        LEFT JOIN users u
        ON u.userident = o.user
    WHERE shipped = 1
    AND user = :user
    AND date >= DATE(NOW() - INTERVAL 30 DAY)

:user being their user ID. Column 2 of this result gives how much a customer has spent in the last 30 days, if it's over 100, then they get the bonus.

I want to display to the user which day they'll leave the loyalty program. Something like "x days until bonus expires", but how do I do this?

Take today's date, March 16th, and a user's order history:

id | tot  |    date
-----------------------
84    38     2016-03-05
76    21     2016-02-29
74    49     2016-02-20
61    42     2015-12-28

This user is part of the loyalty program now but leaves it on March 20th. What SQL could I do which returns how many days (4) a user has left on the loyalty program?

If the user then placed another order:

id | tot  |    date
-----------------------
87    12     2016-03-09

They're still in the loyalty program until the 20th, so the days remaining doesn't change in this instance, but if the total were 50 instead, then they instead leave the program on the 29th (so instead of 4 days it's 13 days remaining). For what it's worth, I care only about 30 days prior to the current date. No consideration for months with 28, 29, 31 days is needed.

Some create table code:

create table users (
    userident int,
    username varchar(100)
);

insert into users values 
    (1, 'Bob');

create table orders (
    id int,
    user int,
    shipped int,
    date date,
    total decimal(6,2),
    shipcost decimal(3,2)
);

insert into orders values
    (84, 1, 1, '2016-03-05', 40.50, 2.50),
    (76, 1, 1, '2016-02-29', 22.00, 1.00),
    (74, 1, 1, '2016-02-20', 56.31, 7.31),
    (61, 1, 1, '2015-12-28', 43.10, 1.10);

An example output of what I'm looking for is:

userident | username | days_left
--------------------------------
    1          Bob        4

This is using March 16th as today for use with DATE(NOW()) to remain consistent with the previous bits of the question.

Answer 1

The following is basically how to do what you want. Note that references to "30 days" are rough estimates and what you may be looking for is "29 days" or "31 days" as works to get the exact date that you want.

1. Retrieve the list of dates and amounts that are still active, i.e., within the last 30 days (as you did in your example), as a table (I'll call it Active) like the one you showed.

2. Join that new table (Active) with the original table where a row from Active is joined to all of the rows of the original table using the date fields. Compute a total of the amounts from the original table. The new table would have a Date field from Active and a Totol field that is the sum of all the amounts in the joined records from the original table.

3. Select from the resulting table all records where the Amount is greater than 100.00 and create a new table with Date and the minimum Amount of those records.

4. Compute 30 days ahead from those dates to find the ending date of their loyalty program.