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-04-10', 'user_01'),
('2020-05-15', 'user_01'),
('2020-09-08', 'user_02'),
('2020-11-23', 'user_03'),
('2020-01-03', 'user_04'),
('2020-06-03', 'user_04'),
('2020-06-03', 'user_04'),
('2020-07-01', 'user_05'),
('2020-09-24', 'user_06'),
('2021-05-02', 'user_01'),
('2021-05-05', 'user_02'),
('2021-05-12', 'user_03'),
('2021-05-19', 'user_03'),
('2021-05-20', 'user_07'),
('2021-06-08', 'user_04'),
('2021-06-20', 'user_05'),
('2021-06-21', 'user_05'),
('2021-06-25', 'user_08');
18 rows affected
SELECT c.order_date, c.customer
FROM (SELECT c.*,
LAG(order_date) OVER (PARTITION BY customer) as prev_order_date
FROM customers c
) c
WHERE c.order_date >= '2021-05-01' AND c.order_date <= '2021-06-30' AND
c.prev_order_date >= c.order_date - INTERVAL '12 month'
| order_date | customer |
|---|---|
| 2021-05-02 | user_01 |
| 2021-05-05 | user_02 |
| 2021-05-19 | user_03 |
| 2021-06-21 | user_05 |
| 2021-06-20 | user_05 |
SELECT c.order_date, c.customer
FROM (SELECT c.*,
LAG(order_date) OVER (PARTITION BY customer) as prev_order_date
FROM customers c
) c
WHERE c.order_date >= '2021-05-01' AND c.order_date < '2021-06-01' AND
c.prev_order_date >= c.order_date - INTERVAL '12 month'
| order_date | customer |
|---|---|
| 2021-05-02 | user_01 |
| 2021-05-05 | user_02 |
| 2021-05-19 | user_03 |
SELECT c.order_date, c.customer
FROM (SELECT c.*,
LAG(order_date) OVER (PARTITION BY customer) as prev_order_date
FROM customers c
) c
WHERE c.order_date >= '2021-06-01' AND c.order_date < '2021-07-01' AND
c.prev_order_date >= c.order_date - INTERVAL '12 month'
| order_date | customer |
|---|---|
| 2021-06-21 | user_05 |
| 2021-06-20 | user_05 |