Postgres 13

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

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