By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
DROP TABLE IF EXISTS users_churn;
create table
users_churn(
id SERIAL PRIMARY KEY,
users varchar(10) not null,
dates timestamp
);
insert into users_churn(users, dates)
values
('1ab7', '2022-01-04 10:22'),
('1ab7', '2022-04-26 18:30'),
('1ab7', '2022-05-12 20:10'),
('1ab7', '2022-07-02 20:55'),
('3ac5', '2022-02-05 05:12'),
('3ac5', '2022-04-09 07:17'),
('3ac5', '2022-07-03 04:19');
select * from users_churn;
DROP TABLE
CREATE TABLE
INSERT 0 7
| id | users | dates |
|---|---|---|
| 1 | 1ab7 | 2022-01-04 10:22:00 |
| 2 | 1ab7 | 2022-04-26 18:30:00 |
| 3 | 1ab7 | 2022-05-12 20:10:00 |
| 4 | 1ab7 | 2022-07-02 20:55:00 |
| 5 | 3ac5 | 2022-02-05 05:12:00 |
| 6 | 3ac5 | 2022-04-09 07:17:00 |
| 7 | 3ac5 | 2022-07-03 04:19:00 |
SELECT 7
with churn as (
select c.users,
c.dates,
date_trunc('month', c.dates) as mdate,
row_number() over (partition by c.users
order by c.dates) as rn_all,
row_number() over (partition by c.users, date_trunc('month', c.dates)
order by c.dates) as rn_month
from users_churn c
), months as (
select generate_series(
min(c.mdate),
current_date,
interval '1 month'
)::timestamp as mdate
from churn c
)
select c.users,
coalesce(c1.dates, m.mdate) as dates,
case
when c1.rn_all = 1 then 'first_purchase'
when c1.users is null then 'churn'
else 'retained'
end as status
from churn c
join months m
on m.mdate >= c.mdate
and c.rn_all = 1
left join churn c1
on c1.users = c.users
and c1.mdate = m.mdate
and c.rn_month = 1
order by c.users, m.mdate;
| users | dates | status |
|---|---|---|
| 1ab7 | 2022-01-04 10:22:00 | first_purchase |
| 1ab7 | 2022-02-01 00:00:00 | churn |
| 1ab7 | 2022-03-01 00:00:00 | churn |
| 1ab7 | 2022-04-26 18:30:00 | retained |
| 1ab7 | 2022-05-12 20:10:00 | retained |
| 1ab7 | 2022-06-01 00:00:00 | churn |
| 1ab7 | 2022-07-02 20:55:00 | retained |
| 1ab7 | 2022-08-01 00:00:00 | churn |
| 1ab7 | 2022-09-01 00:00:00 | churn |
| 1ab7 | 2022-10-01 00:00:00 | churn |
| 1ab7 | 2022-11-01 00:00:00 | churn |
| 1ab7 | 2022-12-01 00:00:00 | churn |
| 1ab7 | 2023-01-01 00:00:00 | churn |
| 3ac5 | 2022-02-05 05:12:00 | first_purchase |
| 3ac5 | 2022-03-01 00:00:00 | churn |
| 3ac5 | 2022-04-09 07:17:00 | retained |
| 3ac5 | 2022-05-01 00:00:00 | churn |
| 3ac5 | 2022-06-01 00:00:00 | churn |
| 3ac5 | 2022-07-03 04:19:00 | retained |
| 3ac5 | 2022-08-01 00:00:00 | churn |
| 3ac5 | 2022-09-01 00:00:00 | churn |
| 3ac5 | 2022-10-01 00:00:00 | churn |
| 3ac5 | 2022-11-01 00:00:00 | churn |
| 3ac5 | 2022-12-01 00:00:00 | churn |
| 3ac5 | 2023-01-01 00:00:00 | churn |
SELECT 25