convert dplyr to sql

Question

I have data structured as follows:


id  start       end          cancelled
1   2020-01-01  2020-12-31   2021-01-10
1   2021-02-01  2022-01-31   NA
2   2020-01-01  2020-12-31   NA
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   NA

These data represent club memberships and the goal is to extract those members who cancel their membership and later re-join. In particular I am interested in the number of days between cancellation and re-joining.

In R I can do:

dat <- structure(list(id = c(1, 1, 2, 3, 3, 3), start = c("2020-01-01", 
"2021-02-01", "2020-01-01", "2020-01-01", "2020-07-10", "2021-02-02"
), end = c("2020-12-31", "2022-01-31", "2020-12-31", "2020-06-30", 
"2021-01-09", "2021-08-01"), cancelled = c("2021-01-10", NA, 
NA, "2020-07-01", "2021-01-31", NA)), class = "data.frame", row.names = c(NA, 
-6L)) %>%

dat[,-1] <- lapply(dat[,-1], as.Date)
dat %>%
group_by(id) %>%
  summarize(
    rejoin_date = start[-1],
    time_to_rejoin = as.numeric(start[-1] - cancelled[-n()], units="days")
  ) %>% drop_na(time_to_rejoin) %>%
  ungroup()

where drop_na(time_to_rejoin) handles the cases where a member has multiple concurrent uncancelled memberships, which results in:

# A tibble: 3 x 3

     id rejoin_date time_to_rejoin
      1 2021-02-01              22
      3 2020-07-10               9
      3 2021-02-02               2

How can I do this in MySQL ?

CREATE TABLE IF NOT EXISTS `dat` (
  `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'),
  ('2', '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 )

http://sqlfiddle.com/#!9/252e6d6

user_10138734 · Answer

SELECT t1.id, 
       COALESCE(t1.cancelled, t1.end) `end`, 
       t2.start next_start, 
       DATEDIFF(t2.start, COALESCE(t1.cancelled, t1.end)) gap
FROM dat t1
JOIN dat t2 ON t1.id = t2.id 
           AND COALESCE(t1.cancelled, t1.end) < t2.start
WHERE NOT EXISTS ( SELECT NULL
                   FROM dat t3
                   WHERE t1.id = t3.id
                     AND COALESCE(t1.cancelled, t1.end) < t3.start
                     AND COALESCE(t3.cancelled, t3.end) < t2.start )

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

PS. Errorneous src data (typo?) in the fiddle fixed.

PPS. If you do not need to return adjacent periods then add HAVING gap > 1 to the most end of the query.

Could you add a description of how it works ? – Joe King

t1 and t2 table copies are used for to retrieve "previous" and "next" periods.

t3 allows to ensure that they are adjacent (there is no any period between the periods taken from t1 and t2).

I.e. if we have 3 periods then JOIN produces 3 pairs (1-2), (2-3), (1-3), but WHERE removes the last pair because there exists period 2 between periods 1 and 3.

COALESCE is used for to take cancelled if it is set and end is it is not set (is NULL).

convert dplyr to sql

1 Answers