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
    *,
    first_value(month) OVER (PARTITION BY customer_id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
    COUNT(*) OVER (PARTITION BY customer_id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM (
    SELECT DISTINCT
        date_trunc('month', purchase_date) as month,
        customer_id
    FROM mytable
) s


month
customer_id
first_value
count


2015-07-01 00:00:00
12
2015-07-01 00:00:00
1

2015-08-01 00:00:00
14
2015-08-01 00:00:00
1

2015-05-01 00:00:00
15
2015-05-01 00:00:00
1

2015-06-01 00:00:00
15
2015-05-01 00:00:00
2

2015-07-01 00:00:00
15
2015-05-01 00:00:00
3

2015-07-01 00:00:00
17
2015-07-01 00:00:00
1

2015-08-01 00:00:00
17
2015-07-01 00:00:00
2



SELECT 7


SELECT
    *,
    age(month, first_value(month) OVER (PARTITION BY customer_id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) as months,
    COUNT(*) OVER (PARTITION BY customer_id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM (
    SELECT DISTINCT
        date_trunc('month', purchase_date) as month,
        customer_id
    FROM mytable
) s


month
customer_id
months
count


2015-07-01 00:00:00
12
00:00:00
1

2015-08-01 00:00:00
14
00:00:00
1

2015-05-01 00:00:00
15
00:00:00
1

2015-06-01 00:00:00
15
1 mon
2

2015-07-01 00:00:00
15
2 mons
3

2015-07-01 00:00:00
17
00:00:00
1

2015-08-01 00:00:00
17
1 mon
2



SELECT 7


SELECT
    customer_id
FROM (
    SELECT
        *,
        age(month, first_value(month) OVER (PARTITION BY customer_id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) as months,
        COUNT(*) OVER (PARTITION BY customer_id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    FROM (
        SELECT DISTINCT
            date_trunc('month', purchase_date) as month,
            customer_id
        FROM mytable
    ) s
) s
WHERE months = interval '2 months' and 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

month	customer_id	months	count
2015-07-01 00:00:00	12	00:00:00	1
2015-08-01 00:00:00	14	00:00:00	1
2015-05-01 00:00:00	15	00:00:00	1
2015-06-01 00:00:00	15	1 mon	2
2015-07-01 00:00:00	15	2 mons	3
2015-07-01 00:00:00	17	00:00:00	1
2015-08-01 00:00:00	17	1 mon	2