2

The setup: people pay for membership subscriptions, they can pay before or after their expiry date, so there are gaps and overlaps. In case of a gap, subscription starts anew, in case of overlapping it adds to the current one. Here's how the table looks like:

typical set of records for a user

So for each new renewal we save a datestamp and its term in months then calculate current expiry on the fly, here's a snippet of code that produces an actual expiry for every account.

SELECT max(z.expiry) as expiry, z.user_id FROM (
  SELECT @exp := IF(user_id = @usr, GREATEST(@exp, created), created) + INTERVAL term MONTH AS expiry,
  @usr := user_id AS user_id    
  FROM memrecords
  JOIN (SELECT @exp := "1975-01-01 00:00:00", @usr := 0) dummy
  ORDER BY user_id ASC, created ASC) AS z
GROUP BY z.user_id;

So far it works kinda all right but I wanted to optimise things a bit by storing expiry dates in a separate table updated with triggers.

The problem is that a) MySQL 8 fires warnings about setting variables in expressions being deprecated, b) I feel that it's a job for a recursive cte and/or window functions but can't figure it out myself.

Fiddle link https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9dcca75bd52c76504ca29ce30e88bf1c

Any ideas are welcome!

5
  • 1
    Hi, and welcome to dba.se! Could you please go to dbfiddle.uk and construct a fiddle with your table structure and your data? This helps create a single point of truth for the question and eliminate duplication of effort on behalf of those trying to help you! Also, you could also include your expiry_date data.
    – Vérace
    Jul 25, 2022 at 8:49
  • Thanks, just added the fiddle link;
    – tgv
    Jul 25, 2022 at 11:55
  • And sorry if I'm not clear enough, the whole drama is that expiry date is dynamic and depends on previous rows, and that is most probably not the healthiest architecture, and I'd like to change it to be future-proof
    – tgv
    Jul 25, 2022 at 12:02
  • Show desired output for your fiddle data, with explanations.
    – Akina
    Jul 25, 2022 at 12:55
  • Desired output should be exactly the same, I'm just hoping to get help with refactoring the code to comply. I want to avoid variables, in particular.
    – tgv
    Jul 25, 2022 at 13:27

1 Answer 1

0
WITH RECURSIVE
cte1 AS (  -- enumerate rows
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created) rn
    FROM memrecords
),
cte2 AS (  -- calculate expire date recursively
    SELECT *,
           created + INTERVAL term MONTH AS expired
    FROM cte1
    WHERE rn = 1
  UNION ALL
    SELECT cte1.*,
           GREATEST(cte2.expired, cte1.created) + INTERVAL cte1.term MONTH
    FROM cte1
    JOIN cte2 USING (user_id)
    WHERE cte1.rn = cte2.rn + 1
),
cte3 AS (  -- get last row number
    SELECT user_id,
           MAX(rn) rn
    FROM cte1
    GROUP BY 1
)
-- obtain needed data
SELECT user_id, cte2.expired
FROM cte3 
NATURAL JOIN cte2

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ca1f1e0b931b1a500fd6bc5604b41f07

4
  • Thank you very much, that's an elegant solution. The result is different though, even in a set of six users two got wrong expiry dates (803 and 804).
    – tgv
    Jul 25, 2022 at 14:54
  • It looks like the results match when there are only overlaps (i.e. they renew before expiry) and differ when there are gaps in the timeline. User id = 804 renewed for 12 months on 2022-01-15 and your script shows their expiry as 2022-07-15.
    – tgv
    Jul 25, 2022 at 15:33
  • @tgv Misprint (must be INTERVAL cte1.term MONTH, not cte2.term). I'll fix shortly... done.
    – Akina
    Jul 25, 2022 at 16:08
  • Fantastic, really appreciate your time. I think from this point I'll manage handling the grace period and other smaller things myself. Have a nice day!
    – tgv
    Jul 26, 2022 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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