By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- test data table
CREATE TABLE test (userid INT, pdate DATE, type VARCHAR(8));
-- test data
INSERT INTO test VALUES
(1,'2020-01-01','PURCHASE'),
(1,'2020-01-02','RENEW'),
(1,'2020-01-03','RENEW'),
(1,'2020-01-04','PURCHASE'),
(1,'2020-01-05','PURCHASE'),
(1,'2020-01-06','RENEW'),
(1,'2020-01-07','RENEW'),
(1,'2020-01-08','RENEW'),
(1,'2020-01-09','RENEW'),
(1,'2020-01-10','PURCHASE'),
(2,'2020-01-01','PURCHASE'),
(2,'2020-01-02','RENEW'),
(2,'2020-01-03','PURCHASE'),
(2,'2020-01-04','RENEW'),
(2,'2020-01-05','PURCHASE'),
(2,'2020-01-06','PURCHASE'),
(2,'2020-01-07','RENEW'),
(2,'2020-01-08','RENEW'),
(2,'2020-01-09','RENEW'),
(2,'2020-01-10','RENEW');
-- step 1 - get previous and next purchases dates pairs
SELECT t1.userid, t1.pdate prevdate, COALESCE(MIN(t2.pdate), '2099-01-01') nextdate
FROM test t1
LEFT JOIN test t2 ON t1.userid = t2.userid
AND t1.pdate < t2.pdate
AND t2.type = 'PURCHASE'
WHERE t1.type = 'PURCHASE'
GROUP BY t1.userid, t1.pdate;
| userid | prevdate | nextdate |
|---|---|---|
| 1 | 2020-01-01 | 2020-01-04 |
| 1 | 2020-01-04 | 2020-01-05 |
| 1 | 2020-01-05 | 2020-01-10 |
| 2 | 2020-01-01 | 2020-01-03 |
| 2 | 2020-01-03 | 2020-01-05 |
| 2 | 2020-01-05 | 2020-01-06 |
| 1 | 2020-01-10 | 2099-01-01 |
| 2 | 2020-01-06 | 2099-01-01 |
-- step 2 calculate renewals count for each dates pair
SELECT sq1.userid, sq1.prevdate, COUNT(CASE WHEN t3.type = 'RENEW' THEN 1 END) renewals
FROM test t3
LEFT JOIN ( SELECT t1.userid, t1.pdate prevdate, COALESCE(MIN(t2.pdate), '2099-01-01') nextdate
FROM test t1
LEFT JOIN test t2 ON t1.userid = t2.userid
AND t1.pdate < t2.pdate
AND t2.type = 'PURCHASE'
WHERE t1.type = 'PURCHASE'
GROUP BY t1.userid, t1.pdate ) sq1 ON t3.userid = sq1.userid
AND t3.pdate BETWEEN sq1.prevdate AND sq1.nextdate
GROUP BY sq1.userid, sq1.prevdate;
| userid | prevdate | renewals |
|---|---|---|
| 1 | 2020-01-01 | 2 |
| 1 | 2020-01-04 | 0 |
| 1 | 2020-01-05 | 4 |
| 1 | 2020-01-10 | 0 |
| 2 | 2020-01-01 | 1 |
| 2 | 2020-01-03 | 1 |
| 2 | 2020-01-05 | 0 |
| 2 | 2020-01-06 | 4 |
-- get statistic
SELECT sq2.renewals,
COUNT(sq2.renewals) renewalscount
FROM ( SELECT sq1.userid,
sq1.prevdate,
COUNT(CASE WHEN t3.type = 'RENEW'
THEN 1 END) renewals
FROM test t3
LEFT JOIN ( SELECT t1.userid,
t1.pdate prevdate,
COALESCE(MIN(t2.pdate), '2099-01-01') nextdate
FROM test t1
LEFT JOIN test t2 ON t1.userid = t2.userid
AND t1.pdate < t2.pdate
AND t2.type = 'PURCHASE'
WHERE t1.type = 'PURCHASE'
GROUP BY t1.userid, t1.pdate ) sq1 ON t3.userid = sq1.userid
AND t3.pdate BETWEEN sq1.prevdate
AND sq1.nextdate
GROUP BY sq1.userid,
sq1.prevdate ) sq2
GROUP BY sq2.renewals
ORDER BY sq2.renewals;
| renewals | renewalscount |
|---|---|
| 0 | 3 |
| 1 | 2 |
| 2 | 1 |
| 4 | 2 |