Postgres 14

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 activity (
    id integer NOT NULL,
    start_date date NOT NULL
);

CREATE TABLE account (
    id integer NOT NULL,
    name varchar NOT NULL
);

CREATE TABLE contact (
    id integer NOT NULL,
    account_id integer NOT NULL,
    name varchar NOT NULL
);

CREATE TABLE activity_contact (
    id integer NOT NULL,
    contact_id integer NOT NULL,
    activity_id integer NOT NULL
);


INSERT INTO activity(id, start_date)
VALUES
(1, '2021-11-03'),
(2, '2021-10-03'),
(3, '2021-11-02');

INSERT INTO account(id, name)
VALUES
(1, 'Test Account');

INSERT INTO contact(id, account_id, name)
VALUES
(1, 1, 'John'),
(2, 1, 'Kevin');

INSERT INTO activity_contact(id, contact_id, activity_id)
VALUES
(1, 1, 1),
(2, 2, 1),
(3, 2, 2),
(4, 1, 3);
3 rows affected

1 rows affected

2 rows affected

4 rows affected

SELECT con.account_id AS accountid
     , count(DISTINCT activity_id) FILTER (WHERE act.start_date >= date_trunc('month', LOCALTIMESTAMP - interval '1 mon')
                                           AND   act.start_date <  date_trunc('month', LOCALTIMESTAMP)) AS last_month
     , count(DISTINCT activity_id) FILTER (WHERE act.start_date >= date_trunc('month', LOCALTIMESTAMP - interval '2 mon')
                                           AND   act.start_date <  date_trunc('month', LOCALTIMESTAMP - interval '1 mon')) AS prev_month
FROM   activity         act
JOIN   activity_contact aco ON aco.activity_id = act.id
JOIN   contact          con ON con.id = aco.contact_id
WHERE  act.start_date >= date_trunc('month', LOCALTIMESTAMP - interval '2 mon')
AND    act.start_date <  date_trunc('month', LOCALTIMESTAMP)
GROUP  BY 1;


accountid
last_month
prev_month


1
2
1


-- simpler
SELECT con.account_id AS accountid
     , date_trunc('month', act.start_date) AS mon
     , count(DISTINCT activity_id) AS dist_count
FROM   activity         act
JOIN   activity_contact aco ON aco.activity_id = act.id
JOIN   contact          con ON con.id = aco.contact_id
-- JOIN   account          acc ON con.account_id = acc.id  -- noise
WHERE  act.start_date >= date_trunc('month', LOCALTIMESTAMP - interval '2 mon')
AND    act.start_date <  date_trunc('month', LOCALTIMESTAMP)
GROUP  BY 1, 2
ORDER  BY 1, 2 DESC;


accountid
mon
dist_count


1
2021-11-01 00:00:00+00
2

1
2021-10-01 00:00:00+01
1