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?.
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
order_date DATE,
customer VARCHAR(255)
);
INSERT INTO customers
(order_date, customer)
VALUES
('2020-03-10', 'user_01'),
('2020-03-15', 'user_01'),
('2020-03-18', 'user_02'),
('2020-03-26', 'user_03'),
('2020-04-12', 'user_04'),
('2020-04-19', 'user_05'),
('2020-04-23', 'user_06'),
('2021-03-09', 'user_01'),
('2021-03-17', 'user_07'),
('2021-04-03', 'user_02'),
('2021-04-18', 'user_05'),
('2021-04-20', 'user_08');
12 rows affected
/* Query for March */
SELECT
(c1.order_date + interval '12 month')::date as churn_date,
c1.customer
FROM customers c1
WHERE c1.order_date BETWEEN '2020-03-01 00:00:00' AND '2020-03-31 23:59:59'
AND NOT EXISTS
(SELECT
c2.customer
FROM customers c2
WHERE c2.order_date BETWEEN '2020-04-01 00:00:00' AND '2021-03-31 23:59:59'
AND c2.customer = c1.customer)
ORDER BY 1,2;
| churn_date | customer |
|---|---|
| 2021-03-18 | user_02 |
| 2021-03-26 | user_03 |
/* Query for April */
SELECT
(c1.order_date + interval '12 month')::date as churn_date,
c1.customer
FROM customers c1
WHERE c1.order_date BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 23:59:59'
AND NOT EXISTS
(SELECT
c2.customer
FROM customers c2
WHERE c2.order_date BETWEEN '2020-05-01 00:00:00' AND '2021-04-30 23:59:59'
AND c2.customer = c1.customer)
ORDER BY 1,2;
| churn_date | customer |
|---|---|
| 2021-04-12 | user_04 |
| 2021-04-23 | user_06 |
SELECT gs.month, c.customer, MAX(c.order_date + interval '12 month')::date as churn_date
FROM customers c CROSS JOIN
GENERATE_SERIES('2021-03-01'::date, '2021-04-01'::date, interval '1 month') gs(month)
WHERE c.order_date < gs.month + interval '1 month'
GROUP BY gs.month, c.customer
HAVING DATE_TRUNC('month', MAX(c.order_date)) = DATE_TRUNC('month', gs.month) - interval '12 month'
| month | customer | churn_date |
|---|---|---|
| 2021-03-01 00:00:00+00 | user_02 | 2021-03-18 |
| 2021-03-01 00:00:00+00 | user_03 | 2021-03-26 |
| 2021-04-01 00:00:00+01 | user_04 | 2021-04-12 |
| 2021-04-01 00:00:00+01 | user_06 | 2021-04-23 |