Calculating membership expiry for overlapping subscriptions

All we need is an easy explanation of the problem, so here it is.

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:

Calculating membership expiry for overlapping subscriptions

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!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply