Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE subscriptions (
    customer_id INT NOT NULL,
    membership_start_date DATE NOT NULL,
    membership_end_date DATE NOT NULL,
    membership_status VARCHAR NOT NULL CHECK (membership_status IN ('Paid', 'Free', 'Non-member'))
);
INSERT INTO subscriptions (customer_id,membership_start_date,membership_end_date,membership_status) VALUES
(6234,'2020-01-20','2020-04-12','Paid'),
(8112,'2020-02-10','2020-03-10','Free'),
(9543,'2020-02-15','2020-04-01','Non-member'),
(7890,'2020-03-01','2020-08-16','Paid'),
(8112,'2020-03-10','2020-05-10','Paid'),
(9543,'2020-04-01','2020-05-01','Free'),
(5501,'2020-04-01','2020-09-15','Non-member'),
(6234,'2020-04-12','2020-07-12','Non-member'),
(9543,'2020-05-01','2020-07-16','Paid'),
(8112,'2020-05-10','2020-08-25','Non-member'),
(6234,'2020-07-12','2020-09-28','Paid'),
(9543,'2020-07-16','2020-10-16','Non-member'),
(7890,'2020-08-16','2020-11-16','Non-member'),
(8112,'2020-08-25','2021-02-01','Non-member'),
(5501,'2020-09-15','2020-10-15','Free'),
(6234,'2020-09-28','2021-09-28','Paid'),
(5501,'2020-10-15','2020-11-15','Free'),
(9543,'2020-10-16','2021-02-08','Paid'),
(5501,'2020-11-15','2021-04-10','Non-member'),
(7890,'2020-11-16','2021-02-13','Paid'),
(8112,'2021-02-01','2021-04-20','Paid'),
(9543,'2021-02-08','2021-03-24','Non-member'),
(7890,'2021-02-13','2021-07-16','Non-member'),
(9543,'2021-03-24','2021-04-24','Free'),
(5501,'2021-04-10','2021-05-10','Free'),
(8112,'2021-04-20','2021-07-20','Non-member'),
(9543,'2021-04-24','2021-05-24','Free'),
(5501,'2021-05-10','2022-05-10','Paid'),
(9543,'2021-05-24','2021-08-24','Paid'),
(7890,'2021-07-16','2022-07-16','Paid'),
(8112,'2021-07-20','2021-08-20','Free'),
(8112,'2021-08-20','2021-11-20','Paid'),
(9543,'2021-08-24','2021-11-11','Non-member'),
(6234,'2021-09-28','2021-12-28','Non-member'),
(9543,'2021-11-11','2021-12-11','Free'),
(8112,'2021-11-20','2022-01-20','Paid'),
(9543,'2021-12-11','2022-01-11','Free'),
(6234,'2021-12-28','2022-02-28','Free'),
(9543,'2022-01-11','2022-04-11','Non-member'),
(8112,'2022-01-20','2022-04-18','Non-member'),
(6234,'2022-02-28','2022-05-14','Paid'),
(9543,'2022-04-11','2022-06-25','Non-member'),
(8112,'2022-04-18','2022-09-02','Non-member'),
(5501,'2022-05-10','2022-08-10','Non-member'),
(6234,'2022-05-14','2022-09-12','Non-member'),
(9543,'2022-06-25','2022-07-25','Free'),
(7890,'2022-07-16','2022-08-16','Free'),
(9543,'2022-07-25','2022-08-25','Free'),
(5501,'2022-08-10','2023-01-05','Non-member'),
(7890,'2022-08-16','2022-11-16','Paid'),
(9543,'2022-08-25','2023-02-15','Non-member'),
(8112,'2022-09-02','2022-10-02','Free'),
(6234,'2022-09-12','2022-12-12','Non-member'),
(8112,'2022-10-02','2022-11-02','Free'),
(8112,'2022-11-02','2023-02-05','Non-member'),
(7890,'2022-11-16','2023-03-05','Non-member'),
(6234,'2022-12-12','2023-01-12','Free'),
(5501,'2023-01-05','2023-02-05','Free'),
(6234,'2023-01-12','2023-02-12','Free'),
(5501,'2023-02-05','2023-03-05','Free'),
(8112,'2023-02-05','2023-03-05','Free'),
(6234,'2023-02-12','2023-06-15','Paid'),
(9543,'2023-02-15','2023-03-15','Free'),
(7890,'2023-03-05','2023-04-05','Free'),
(5501,'2023-03-05','2023-06-05','Non-member'),
(8112,'2023-03-05','2023-06-19','Non-member'),
(9543,'2023-03-15','2023-04-15','Free'),
(7890,'2023-04-05','2023-08-08','Paid'),
(9543,'2023-04-15','2023-05-15','Free'),
(9543,'2023-05-15','2023-06-15','Free'),
(5501,'2023-06-05','2023-07-05','Free'),
(9543,'2023-06-15','2023-07-15','Free'),
(6234,'2023-06-15','2023-09-15','Non-member'),
(8112,'2023-06-19','2024-06-19','Paid'),
(5501,'2023-07-05','2023-10-05','Paid'),
(9543,'2023-07-15','2023-09-28','Paid'),
(7890,'2023-08-08','2023-11-20','Non-member'),
(6234,'2023-09-15','2023-10-15','Free'),
(9543,'2023-09-28','2024-03-01','Non-member'),
(5501,'2023-10-05','2024-01-05','Paid'),
(6234,'2023-10-15','2024-01-15','Paid'),
(7890,'2023-11-20','2023-12-20','Free'),
(7890,'2023-12-20','2024-02-23','Non-member'),
(5501,'2024-01-05','2024-04-22','Non-member'),
(6234,'2024-01-15','2024-04-03','Non-member'),
(7890,'2024-02-23','2024-03-23','Free'),
(9543,'2024-03-01','2024-04-01','Free'),
(7890,'2024-03-23','2025-03-23','Paid'),
(9543,'2024-04-01','2025-04-01','Paid'),
(6234,'2024-04-03','2024-05-03','Free'),
(5501,'2024-04-22','2024-05-22','Free'),
(6234,'2024-05-03','2024-06-03','Free'),
(5501,'2024-05-22','2025-05-22','Paid'),
(6234,'2024-06-03','2024-09-03','Paid'),
(8112,'2024-06-19','2024-07-19','Free'),
(8112,'2024-07-19','2024-10-19','Paid'),
(6234,'2024-09-03','2025-01-03','Paid'),
(8112,'2024-10-19','2025-01-19','Paid'),
(6234,'2025-01-03','2025-02-03','Free'),
(8112,'2025-01-19','2025-02-19','Free'),
(6234,'2025-02-03','2025-05-03','Non-member'),
(8112,'2025-02-19','2025-03-19','Free'),
(7890,'2025-03-23','2025-06-23','Non-member'),
(9543,'2025-04-01','2025-07-01','Non-member'),
(5501,'2025-05-22','2025-06-22','Free');
SELECT * FROM subscriptions ORDER BY 1,2;

CREATE TABLE



INSERT 0 105




customer_id
membership_start_date
membership_end_date
membership_status


5501
2020-04-01
2020-09-15
Non-member

5501
2020-09-15
2020-10-15
Free

5501
2020-10-15
2020-11-15
Free

5501
2020-11-15
2021-04-10
Non-member

5501
2021-04-10
2021-05-10
Free

5501
2021-05-10
2022-05-10
Paid

5501
2022-05-10
2022-08-10
Non-member

5501
2022-08-10
2023-01-05
Non-member

5501
2023-01-05
2023-02-05
Free

5501
2023-02-05
2023-03-05
Free

5501
2023-03-05
2023-06-05
Non-member

5501
2023-06-05
2023-07-05
Free

5501
2023-07-05
2023-10-05
Paid

5501
2023-10-05
2024-01-05
Paid

5501
2024-01-05
2024-04-22
Non-member

5501
2024-04-22
2024-05-22
Free

5501
2024-05-22
2025-05-22
Paid

5501
2025-05-22
2025-06-22
Free

6234
2020-01-20
2020-04-12
Paid

6234
2020-04-12
2020-07-12
Non-member

6234
2020-07-12
2020-09-28
Paid

6234
2020-09-28
2021-09-28
Paid

6234
2021-09-28
2021-12-28
Non-member

6234
2021-12-28
2022-02-28
Free

6234
2022-02-28
2022-05-14
Paid

6234
2022-05-14
2022-09-12
Non-member

6234
2022-09-12
2022-12-12
Non-member

6234
2022-12-12
2023-01-12
Free

6234
2023-01-12
2023-02-12
Free

6234
2023-02-12
2023-06-15
Paid

6234
2023-06-15
2023-09-15
Non-member

6234
2023-09-15
2023-10-15
Free

6234
2023-10-15
2024-01-15
Paid

6234
2024-01-15
2024-04-03
Non-member

6234
2024-04-03
2024-05-03
Free

6234
2024-05-03
2024-06-03
Free

6234
2024-06-03
2024-09-03
Paid

6234
2024-09-03
2025-01-03
Paid

6234
2025-01-03
2025-02-03
Free

6234
2025-02-03
2025-05-03
Non-member

7890
2020-03-01
2020-08-16
Paid

7890
2020-08-16
2020-11-16
Non-member

7890
2020-11-16
2021-02-13
Paid

7890
2021-02-13
2021-07-16
Non-member

7890
2021-07-16
2022-07-16
Paid

7890
2022-07-16
2022-08-16
Free

7890
2022-08-16
2022-11-16
Paid

7890
2022-11-16
2023-03-05
Non-member

7890
2023-03-05
2023-04-05
Free

7890
2023-04-05
2023-08-08
Paid

7890
2023-08-08
2023-11-20
Non-member

7890
2023-11-20
2023-12-20
Free

7890
2023-12-20
2024-02-23
Non-member

7890
2024-02-23
2024-03-23
Free

7890
2024-03-23
2025-03-23
Paid

7890
2025-03-23
2025-06-23
Non-member

8112
2020-02-10
2020-03-10
Free

8112
2020-03-10
2020-05-10
Paid

8112
2020-05-10
2020-08-25
Non-member

8112
2020-08-25
2021-02-01
Non-member

8112
2021-02-01
2021-04-20
Paid

8112
2021-04-20
2021-07-20
Non-member

8112
2021-07-20
2021-08-20
Free

8112
2021-08-20
2021-11-20
Paid

8112
2021-11-20
2022-01-20
Paid

8112
2022-01-20
2022-04-18
Non-member

8112
2022-04-18
2022-09-02
Non-member

8112
2022-09-02
2022-10-02
Free

8112
2022-10-02
2022-11-02
Free

8112
2022-11-02
2023-02-05
Non-member

8112
2023-02-05
2023-03-05
Free

8112
2023-03-05
2023-06-19
Non-member

8112
2023-06-19
2024-06-19
Paid

8112
2024-06-19
2024-07-19
Free

8112
2024-07-19
2024-10-19
Paid

8112
2024-10-19
2025-01-19
Paid

8112
2025-01-19
2025-02-19
Free

8112
2025-02-19
2025-03-19
Free

9543
2020-02-15
2020-04-01
Non-member

9543
2020-04-01
2020-05-01
Free

9543
2020-05-01
2020-07-16
Paid

9543
2020-07-16
2020-10-16
Non-member

9543
2020-10-16
2021-02-08
Paid

9543
2021-02-08
2021-03-24
Non-member

9543
2021-03-24
2021-04-24
Free

9543
2021-04-24
2021-05-24
Free

9543
2021-05-24
2021-08-24
Paid

9543
2021-08-24
2021-11-11
Non-member

9543
2021-11-11
2021-12-11
Free

9543
2021-12-11
2022-01-11
Free

9543
2022-01-11
2022-04-11
Non-member

9543
2022-04-11
2022-06-25
Non-member

9543
2022-06-25
2022-07-25
Free

9543
2022-07-25
2022-08-25
Free

9543
2022-08-25
2023-02-15
Non-member

9543
2023-02-15
2023-03-15
Free

9543
2023-03-15
2023-04-15
Free

9543
2023-04-15
2023-05-15
Free

9543
2023-05-15
2023-06-15
Free

9543
2023-06-15
2023-07-15
Free

9543
2023-07-15
2023-09-28
Paid

9543
2023-09-28
2024-03-01
Non-member

9543
2024-03-01
2024-04-01
Free

9543
2024-04-01
2025-04-01
Paid

9543
2025-04-01
2025-07-01
Non-member



SELECT 105


CREATE TEMPORARY TABLE actions (status_from VARCHAR, status_to VARCHAR, event VARCHAR);
INSERT INTO actions (status_from, status_to, event) VALUES
('Free','Paid','Convert'),
('Paid','Free','ReverseConvert'),
('Paid','Non-member','Cancel'),
('Free','Non-member','Cancel'),
('Non-member','Paid','ColdStart'),
('Non-member','Free','WarmStart'),
('Non-member','Non-member','Non-member'),
('Paid','Paid','Renewal'),
('Free','Free','Renewal');
SELECT * FROM actions;

CREATE TABLE



INSERT 0 9




status_from
status_to
event


Free
Paid
Convert

Paid
Free
ReverseConvert

Paid
Non-member
Cancel

Free
Non-member
Cancel

Non-member
Paid
ColdStart

Non-member
Free
WarmStart

Non-member
Non-member
Non-member

Paid
Paid
Renewal

Free
Free
Renewal



SELECT 9


WITH cte1 AS (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY membership_start_date) rn
  FROM subscriptions
)
SELECT COALESCE(t1.customer_id, t2.customer_id) customer_id,
       COALESCE(t1.membership_start_date, t2.membership_end_date) change_date,
       actions.event
FROM cte1 t1
FULL JOIN cte1 t2 ON t1.customer_id = t2.customer_id
                 AND t1.rn = t2.rn + 1
JOIN actions ON COALESCE(t1.membership_status, 'Non-member') = actions.status_to
            AND COALESCE(t2.membership_status, 'Non-member') = actions.status_from
ORDER BY 1, 2;


customer_id
change_date
event


5501
2020-04-01
Non-member

5501
2020-09-15
WarmStart

5501
2020-10-15
Renewal

5501
2020-11-15
Cancel

5501
2021-04-10
WarmStart

5501
2021-05-10
Convert

5501
2022-05-10
Cancel

5501
2022-08-10
Non-member

5501
2023-01-05
WarmStart

5501
2023-02-05
Renewal

5501
2023-03-05
Cancel

5501
2023-06-05
WarmStart

5501
2023-07-05
Convert

5501
2023-10-05
Renewal

5501
2024-01-05
Cancel

5501
2024-04-22
WarmStart

5501
2024-05-22
Convert

5501
2025-05-22
ReverseConvert

5501
2025-06-22
Cancel

6234
2020-01-20
ColdStart

6234
2020-04-12
Cancel

6234
2020-07-12
ColdStart

6234
2020-09-28
Renewal

6234
2021-09-28
Cancel

6234
2021-12-28
WarmStart

6234
2022-02-28
Convert

6234
2022-05-14
Cancel

6234
2022-09-12
Non-member

6234
2022-12-12
WarmStart

6234
2023-01-12
Renewal

6234
2023-02-12
Convert

6234
2023-06-15
Cancel

6234
2023-09-15
WarmStart

6234
2023-10-15
Convert

6234
2024-01-15
Cancel

6234
2024-04-03
WarmStart

6234
2024-05-03
Renewal

6234
2024-06-03
Convert

6234
2024-09-03
Renewal

6234
2025-01-03
ReverseConvert

6234
2025-02-03
Cancel

6234
2025-05-03
Non-member

7890
2020-03-01
ColdStart

7890
2020-08-16
Cancel

7890
2020-11-16
ColdStart

7890
2021-02-13
Cancel

7890
2021-07-16
ColdStart

7890
2022-07-16
ReverseConvert

7890
2022-08-16
Convert

7890
2022-11-16
Cancel

7890
2023-03-05
WarmStart

7890
2023-04-05
Convert

7890
2023-08-08
Cancel

7890
2023-11-20
WarmStart

7890
2023-12-20
Cancel

7890
2024-02-23
WarmStart

7890
2024-03-23
Convert

7890
2025-03-23
Cancel

7890
2025-06-23
Non-member

8112
2020-02-10
WarmStart

8112
2020-03-10
Convert

8112
2020-05-10
Cancel

8112
2020-08-25
Non-member

8112
2021-02-01
ColdStart

8112
2021-04-20
Cancel

8112
2021-07-20
WarmStart

8112
2021-08-20
Convert

8112
2021-11-20
Renewal

8112
2022-01-20
Cancel

8112
2022-04-18
Non-member

8112
2022-09-02
WarmStart

8112
2022-10-02
Renewal

8112
2022-11-02
Cancel

8112
2023-02-05
WarmStart

8112
2023-03-05
Cancel

8112
2023-06-19
ColdStart

8112
2024-06-19
ReverseConvert

8112
2024-07-19
Convert

8112
2024-10-19
Renewal

8112
2025-01-19
ReverseConvert

8112
2025-02-19
Renewal

8112
2025-03-19
Cancel

9543
2020-02-15
Non-member

9543
2020-04-01
WarmStart

9543
2020-05-01
Convert

9543
2020-07-16
Cancel

9543
2020-10-16
ColdStart

9543
2021-02-08
Cancel

9543
2021-03-24
WarmStart

9543
2021-04-24
Renewal

9543
2021-05-24
Convert

9543
2021-08-24
Cancel

9543
2021-11-11
WarmStart

9543
2021-12-11
Renewal

9543
2022-01-11
Cancel

9543
2022-04-11
Non-member

9543
2022-06-25
WarmStart

9543
2022-07-25
Renewal

9543
2022-08-25
Cancel

9543
2023-02-15
WarmStart

9543
2023-03-15
Renewal

9543
2023-04-15
Renewal

9543
2023-05-15
Renewal

9543
2023-06-15
Renewal

9543
2023-07-15
Convert

9543
2023-09-28
Cancel

9543
2024-03-01
WarmStart

9543
2024-04-01
Convert

9543
2025-04-01
Cancel

9543
2025-07-01
Non-member



SELECT 110

customer_id	membership_start_date	membership_end_date	membership_status
5501	2020-04-01	2020-09-15	Non-member
5501	2020-09-15	2020-10-15	Free
5501	2020-10-15	2020-11-15	Free
5501	2020-11-15	2021-04-10	Non-member
5501	2021-04-10	2021-05-10	Free
5501	2021-05-10	2022-05-10	Paid
5501	2022-05-10	2022-08-10	Non-member
5501	2022-08-10	2023-01-05	Non-member
5501	2023-01-05	2023-02-05	Free
5501	2023-02-05	2023-03-05	Free
5501	2023-03-05	2023-06-05	Non-member
5501	2023-06-05	2023-07-05	Free
5501	2023-07-05	2023-10-05	Paid
5501	2023-10-05	2024-01-05	Paid
5501	2024-01-05	2024-04-22	Non-member
5501	2024-04-22	2024-05-22	Free
5501	2024-05-22	2025-05-22	Paid
5501	2025-05-22	2025-06-22	Free
6234	2020-01-20	2020-04-12	Paid
6234	2020-04-12	2020-07-12	Non-member
6234	2020-07-12	2020-09-28	Paid
6234	2020-09-28	2021-09-28	Paid
6234	2021-09-28	2021-12-28	Non-member
6234	2021-12-28	2022-02-28	Free
6234	2022-02-28	2022-05-14	Paid
6234	2022-05-14	2022-09-12	Non-member
6234	2022-09-12	2022-12-12	Non-member
6234	2022-12-12	2023-01-12	Free
6234	2023-01-12	2023-02-12	Free
6234	2023-02-12	2023-06-15	Paid
6234	2023-06-15	2023-09-15	Non-member
6234	2023-09-15	2023-10-15	Free
6234	2023-10-15	2024-01-15	Paid
6234	2024-01-15	2024-04-03	Non-member
6234	2024-04-03	2024-05-03	Free
6234	2024-05-03	2024-06-03	Free
6234	2024-06-03	2024-09-03	Paid
6234	2024-09-03	2025-01-03	Paid
6234	2025-01-03	2025-02-03	Free
6234	2025-02-03	2025-05-03	Non-member
7890	2020-03-01	2020-08-16	Paid
7890	2020-08-16	2020-11-16	Non-member
7890	2020-11-16	2021-02-13	Paid
7890	2021-02-13	2021-07-16	Non-member
7890	2021-07-16	2022-07-16	Paid
7890	2022-07-16	2022-08-16	Free
7890	2022-08-16	2022-11-16	Paid
7890	2022-11-16	2023-03-05	Non-member
7890	2023-03-05	2023-04-05	Free
7890	2023-04-05	2023-08-08	Paid
7890	2023-08-08	2023-11-20	Non-member
7890	2023-11-20	2023-12-20	Free
7890	2023-12-20	2024-02-23	Non-member
7890	2024-02-23	2024-03-23	Free
7890	2024-03-23	2025-03-23	Paid
7890	2025-03-23	2025-06-23	Non-member
8112	2020-02-10	2020-03-10	Free
8112	2020-03-10	2020-05-10	Paid
8112	2020-05-10	2020-08-25	Non-member
8112	2020-08-25	2021-02-01	Non-member
8112	2021-02-01	2021-04-20	Paid
8112	2021-04-20	2021-07-20	Non-member
8112	2021-07-20	2021-08-20	Free
8112	2021-08-20	2021-11-20	Paid
8112	2021-11-20	2022-01-20	Paid
8112	2022-01-20	2022-04-18	Non-member
8112	2022-04-18	2022-09-02	Non-member
8112	2022-09-02	2022-10-02	Free
8112	2022-10-02	2022-11-02	Free
8112	2022-11-02	2023-02-05	Non-member
8112	2023-02-05	2023-03-05	Free
8112	2023-03-05	2023-06-19	Non-member
8112	2023-06-19	2024-06-19	Paid
8112	2024-06-19	2024-07-19	Free
8112	2024-07-19	2024-10-19	Paid
8112	2024-10-19	2025-01-19	Paid
8112	2025-01-19	2025-02-19	Free
8112	2025-02-19	2025-03-19	Free
9543	2020-02-15	2020-04-01	Non-member
9543	2020-04-01	2020-05-01	Free
9543	2020-05-01	2020-07-16	Paid
9543	2020-07-16	2020-10-16	Non-member
9543	2020-10-16	2021-02-08	Paid
9543	2021-02-08	2021-03-24	Non-member
9543	2021-03-24	2021-04-24	Free
9543	2021-04-24	2021-05-24	Free
9543	2021-05-24	2021-08-24	Paid
9543	2021-08-24	2021-11-11	Non-member
9543	2021-11-11	2021-12-11	Free
9543	2021-12-11	2022-01-11	Free
9543	2022-01-11	2022-04-11	Non-member
9543	2022-04-11	2022-06-25	Non-member
9543	2022-06-25	2022-07-25	Free
9543	2022-07-25	2022-08-25	Free
9543	2022-08-25	2023-02-15	Non-member
9543	2023-02-15	2023-03-15	Free
9543	2023-03-15	2023-04-15	Free
9543	2023-04-15	2023-05-15	Free
9543	2023-05-15	2023-06-15	Free
9543	2023-06-15	2023-07-15	Free
9543	2023-07-15	2023-09-28	Paid
9543	2023-09-28	2024-03-01	Non-member
9543	2024-03-01	2024-04-01	Free
9543	2024-04-01	2025-04-01	Paid
9543	2025-04-01	2025-07-01	Non-member

status_from	status_to	event
Free	Paid	Convert
Paid	Free	ReverseConvert
Paid	Non-member	Cancel
Free	Non-member	Cancel
Non-member	Paid	ColdStart
Non-member	Free	WarmStart
Non-member	Non-member	Non-member
Paid	Paid	Renewal
Free	Free	Renewal