Difficulty with variable lag length

user_1461667 0
Viewed 52

Apologies for the wording of this question, but I wasn't sure how to describe this problem.

I have data on club memberships, as follows


| id  | start               | end                 | cancelled           |
| --- | ------------------- | ------------------- | ------------------- |
| 1   | 2020-01-01 00:00:00 | 2020-12-31 00:00:00 | 2021-01-10 00:00:00 |
| 1   | 2021-02-01 00:00:00 | 2022-01-31 00:00:00 |                     |
| 2   | 2021-01-01 00:00:00 | 2020-12-31 00:00:00 |                     |
| 3   | 2020-01-01 00:00:00 | 2020-06-30 00:00:00 | 2020-07-01 00:00:00 |
| 3   | 2020-07-10 00:00:00 | 2021-01-09 00:00:00 | 2021-01-31 00:00:00 |
| 3   | 2021-02-02 00:00:00 | 2021-08-01 00:00:00 |                     |
| 4   | 2018-04-28 00:00:00 | 2019-04-28 00:00:00 | 2020-01-03 00:00:00 |
| 4   | 2019-03-07 00:00:00 | 2021-08-01 00:00:00 |                     |
| 4   | 2020-02-22 00:00:00 | 2021-02-22 00:00:00 |                     |

Interest is centered on the time between a membership being cancelled and a new membership being taken out. I have the following code, which was provided in Gordon Linoff's answer to my previous question

select t.*,
       datediff(start, prev_cancelled) as num_days_since_cancel
from (select dt.*,
             lag(cancelled) over (partition by id order by start) as prev_cancelled
      from dt
     ) t
where prev_cancelled is not null;

which produces the following:


| id  | start               | end                 | cancelled           | prev_cancelled      | num_days_since_cancel |
| --- | ------------------- | ------------------- | ------------------- | ------------------- | --------------------- |
| 1   | 2021-02-01 00:00:00 | 2022-01-31 00:00:00 |                     | 2021-01-10 00:00:00 | 22                    |
| 3   | 2020-07-10 00:00:00 | 2021-01-09 00:00:00 | 2021-01-31 00:00:00 | 2020-07-01 00:00:00 | 9                     |
| 3   | 2021-02-02 00:00:00 | 2021-08-01 00:00:00 |                     | 2021-01-31 00:00:00 | 2                     |
| 4   | 2019-03-07 00:00:00 | 2021-08-01 00:00:00 |                     | 2020-01-03 00:00:00 | -302                  |

This is the desired output for all except id 4, which should return 50 days, not -302. I understand why it does this, because the lag of 1, so in the case of id 4 we would need a lag of 2, because the 2nd membership had not ended before the first one was cancelled, however I can't find a way to do this. Moreover, although this is an edge case, there is no reason why there should not be 2 or more subsequent unexpired memberships prior to the first one being cancelled.

Perhaps a better approach would be to somehow filter out any unexpired memberships that start subsequent to the first membership and expire subsequent to the first membership being cancelled. Or maybe there is a better way ? Toy Data:

CREATE TABLE IF NOT EXISTS `dt` (
  `id` int(6) unsigned NOT NULL,
  `start` TIMESTAMP,
  `end` TIMESTAMP,
  `cancelled` TIMESTAMP NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `dt` (`id`, `start`, `end`, `cancelled`) VALUES
  ('1', '2020-01-01', '2020-12-31', '2021-01-10'),
  ('1', '2021-02-01', '2022-01-31', NULL ),
  ('2', '2021-01-01', '2020-12-31', NULL ),
  ('3', '2020-01-01', '2020-06-30', '2020-07-01'),
  ('3', '2020-07-10', '2021-01-09', '2021-01-31'),
  ('3', '2021-02-02', '2021-08-01', NULL ),
  ('4', '2018-04-28', '2019-04-28', '2020-01-03' ),
  ('4', '2019-03-07', '2021-08-01', NULL ),
  ('4', '2020-02-22', '2021-02-22', NULL )
mysql sql
3 Answers

You can instead use a cumulative max() on the cancelled:

select t.*,
       datediff(start, prev_cancelled) as num_days_since_cancel
from (select dt.*,
             max(cancelled) over (partition by id order by start) as prev_cancelled
      from dt
     ) t
where prev_cancelled is not null;

This will reach back past the NULL values. It does assume that cancelled dates are ordered by start when they are not NULL. That seems like a reasonable assumption.

user_11440350

Instead of comparing to the cancel date in the previous row you need to compare to the latest cancel date in all previous rows. Standard SQL has a IGNORE NULLS option to achieve this, but MySQL doesn't support it. Luckily in your case it can be rewritten using a Cumulative Max:

select t.*,
       datediff(start, prev_cancelled) as num_days_since_cancel
from (select dt.*,
             max(cancelled) over -- latest date per id
             (partition by id 
              order by start
              rows between unbounded preceding and 1 preceding) as prev_cancelled
      from dt
     ) t
-- remove negative duration
where datediff(start, prev_cancelled) >= 0;

See fiddle

user_25279050

You can select rows where the id is proceeded by other rows with the same id and a canceled date that is less than or equal to start for the given row:

with vals as (
    select d.id, d.start, max(d1.cancelled) m 
    from dt d 
    join dt d1 on (d1.id = d.id and d1.cancelled is not null and d1.cancelled <= d.start) 
    group by d.id, d.start
)
select v.id, v.start rejoin_date, datediff(v.start, v.m) time_to_rejoin from vals v;

See demo.

user_73267380
Related