title: SQL query to get resubscribe date
body: <p><a href="https://i.stack.imgur.com/j9G60.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/j9G60.png" alt="enter image description here" /></a>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.</p>
  <p>I would like to get the last re-subscription date for each subscriber.</p>
  <p>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.</p>
  <p>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.</p>
  <p>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.</p>
  <p>EDIT 1</p>
  <p>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.</p>
  <p>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.</p>
  <p>So in this example, the most recent re-subscription starts on 05/24/22.</p>
  <p>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.</p>
  <p>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.</p>
  <p>I am thinking now that with a bit more work, I could come up with that.</p>
  <p>EDIT 2: here is the sample data from the screen shot:</p>
  <pre><code>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
  </code></pre>
  <p>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.</p>
  <p>Here is the code for the temporary table, where we also want to omit zero amount lines:</p>
  <pre><code>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 &lt;&gt; 0 and p2.userid is NULL;
  </code></pre>
  <p>Then here is the code for the greatest-n-by-group:</p>
  <pre><code>select m.*
  from resubscribe m
  left join resubscribe b on m.userid = b.userid and m.datepaid &lt; b.datepaid
  where b.datepaid is NULL;
  </code></pre>
  <p>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 (&quot;maximum&quot;) and b (&quot;bigger&quot;) 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.</p>
  <p>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.</p>