I have a table of payments of subscribers, with one row per payment. Subscribers pay monthly on the same day of the month. They may quit for a while and come back later on another day of the month. So there can be multiple sequences of payments one month apart, with gaps between the sequences.
I would like to get the last re-subscription date for each subscriber.
Another way to state the problem is to get the row of the most recent sequence of consecutive months that occurs after a gap in the months.
If there is no gap, then the first payment row for that subscriber would be the desired row, with the initial subscription being a degenerate case of a resubscription.
This query seems like it might be a case of a nested greatest-n-per-group, where each sequence would be a group, and then you just take the maximum date of the inner group. Not sure how that would be put together.
EDIT 1
I have added a formatted example of some data. In this display, the subscriber id is not shown, but it would be the same for every row.
If you look in the Paid column, you can see that there are three subscription periods with gaps. The dates are formatted as MM/DD/YY. The first period is from 07/24/21 through 08/24/21. Then there is a gap to the next subscription period, which runs from 11/24/21 through 01/24/22. The third period is from 05/24/22 through 09/24/22.
So in this example, the most recent re-subscription starts on 05/24/22.
Again, it seems that the query should select rows for each subscriber id that have no row exactly one month prior (which in this case would be 07/24/21, 11/24/21, and 05/25/22), and then take the greatest of those rows.
So I am starting to formulate a possible strategy. That would be to create a temporary table with those three rows, and then on that temporary table do a greatest-n-per-group.
I am thinking now that with a bit more work, I could come up with that.
EDIT 2: here is the sample data from the screen shot:
userid, datepaid
104202,102759,0.00,2021-07-24
104203,102759,299.00,2021-07-24
104204,102759,99.00,2021-08-24
104566,102759,99.00,2021-09-24
104910,102759,99.00,2021-10-24
105267,102759,99.00,2021-11-24
105653,102759,99.00,2021-12-24
106040,102759,99.00,2022-01-24
106407,102759,99.00,2022-02-24
106836,102759,99.00,2022-03-24
107205,102759,99.00,2022-04-24
107568,102759,99.00,2022-05-24
107950,102759,99.00,2022-06-24
108292,102759,99.00,2022-07-24
108665,102759,99.00,2022-08-24
109041,102759,99.00,2022-09-24
However, as I said in EDIT 1, the germ of a solution came to me as I was writing that edit, and it does work. It is a temporary table to get the subscription starts for each subscriber, then a greatest-n-by-group to get the most recent sequence.
Here is the code for the temporary table, where we also want to omit zero amount lines:
create temporary table resubscribe
select p1.paidpridtlid, p1.userid, p1.datepaid from paidpridtl p1
left join paidpridtl p2 on p1.userid = p2.userid and DATE_SUB(p1.datepaid, interval 1 month) = p2.datepaid
where p1.amount <> 0 and p2.userid is NULL;
Then here is the code for the greatest-n-by-group:
select m.*
from resubscribe m
left join resubscribe b on m.userid = b.userid and m.datepaid < b.datepaid
where b.datepaid is NULL;
This is probably an ok solution. However, it might be nice to combine the two queries a single query. The trick is that the table for m ("maximum") and b ("bigger") would each be the same sub-select. That seems ugly, and I am not sure if the optimizer would see that the tables are the same and only create one version. Or, there might be a way to write the SQL so that there is only one sub-select.
Sort of a moot point, because the temporary table method is fine in my application as it is a batch job. Still, I would award points for a nice such solution.