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 mytable
    ("purchase_date" timestamp, "customer_id" int)
;
    
INSERT INTO mytable VALUES
    ('2015-05-25 03:24:09+05:30 |', 15),
    ('2015-06-21 06:33:35+05:30 |', 15),
    ('2015-07-02 02:03:32+05:30 |', 17),
    ('2015-07-25 10:20:31+05:30 |', 15),
    ('2015-07-25 10:20:31+05:30 |', 12),
    ('2015-07-26 01:20:31+05:30 |', 17),
    ('2015-08-26 03:24:09+05:30 |', 17),
    ('2015-08-21 03:21:21+05:30 |', 14);
    
SELECT * FROM mytable

CREATE TABLE



INSERT 0 8




purchase_date
customer_id


2015-05-25 03:24:09
15

2015-06-21 06:33:35
15

2015-07-02 02:03:32
17

2015-07-25 10:20:31
15

2015-07-25 10:20:31
12

2015-07-26 01:20:31
17

2015-08-26 03:24:09
17

2015-08-21 03:21:21
14



SELECT 8


SELECT DISTINCT
    date_trunc('month', purchase_date) as month,
    customer_id
FROM mytable


month
customer_id


2015-07-01 00:00:00
17

2015-08-01 00:00:00
17

2015-05-01 00:00:00
15

2015-06-01 00:00:00
15

2015-07-01 00:00:00
15

2015-08-01 00:00:00
14

2015-07-01 00:00:00
12



SELECT 7


SELECT
    customer_id
FROM (
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY customer_id ORDER BY month RANGE BETWEEN interval '2 months' PRECEDING AND CURRENT ROW)
    FROM (
        SELECT DISTINCT
            date_trunc('month', purchase_date) as month,
            customer_id
        FROM mytable
    ) s
) s
WHERE count = 3


customer_id


15



SELECT 1

purchase_date	customer_id
2015-05-25 03:24:09	15
2015-06-21 06:33:35	15
2015-07-02 02:03:32	17
2015-07-25 10:20:31	15
2015-07-25 10:20:31	12
2015-07-26 01:20:31	17
2015-08-26 03:24:09	17
2015-08-21 03:21:21	14

month	customer_id
2015-07-01 00:00:00	17
2015-08-01 00:00:00	17
2015-05-01 00:00:00	15
2015-06-01 00:00:00	15
2015-07-01 00:00:00	15
2015-08-01 00:00:00	14
2015-07-01 00:00:00	12