Postgres 12

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 tbl (transaction_date timestamp, transaction_sale int);
    
INSERT INTO tbl (transaction_date, transaction_sale)
VALUES
    ('2018-10-01 00:00:00', NULL),
    ('2018-11-01 00:00:00', 33),
    ('2018-12-01 00:00:00', NULL),
    ('2019-01-01 00:00:00', NULL),
    ('2019-02-01 00:00:00', NULL),
    ('2019-03-01 00:00:00', 2),
    ('2019-04-01 00:00:00', NULL),
    ('2019-05-01 00:00:00', NULL),
    ('2019-06-01 00:00:00', 10);
9 rows affected

SELECT transaction_date, transaction_sale
     , CASE WHEN transaction_sale IS NOT NULL
            THEN count(*) OVER (PARTITION BY grp) - 1
       END AS count 
FROM  (
   SELECT *
        , count(transaction_sale) OVER (ORDER BY transaction_date DESC) AS grp
   FROM tbl
   ) sub
ORDER  BY transaction_date;


transaction_date
transaction_sale
count


2018-10-01 00:00:00
null
null

2018-11-01 00:00:00
33
1

2018-12-01 00:00:00
null
null

2019-01-01 00:00:00
null
null

2019-02-01 00:00:00
null
null

2019-03-01 00:00:00
2
3

2019-04-01 00:00:00
null
null

2019-05-01 00:00:00
null
null

2019-06-01 00:00:00
10
2


SELECT transaction_date, transaction_sale
     , CASE WHEN transaction_sale IS NOT NULL
            THEN count(*) FILTER (WHERE transaction_sale IS NULL)
                          OVER (PARTITION BY grp)
       END AS count 
FROM  (
   SELECT *
        , count(transaction_sale) OVER (ORDER BY transaction_date DESC) AS grp
   FROM tbl
   ) sub
ORDER  BY transaction_date;


transaction_date
transaction_sale
count


2018-10-01 00:00:00
null
null

2018-11-01 00:00:00
33
1

2018-12-01 00:00:00
null
null

2019-01-01 00:00:00
null
null

2019-02-01 00:00:00
null
null

2019-03-01 00:00:00
2
3

2019-04-01 00:00:00
null
null

2019-05-01 00:00:00
null
null

2019-06-01 00:00:00
10
2

transaction_date	transaction_sale	count
2018-10-01 00:00:00	null	null
2018-11-01 00:00:00	33	1
2018-12-01 00:00:00	null	null
2019-01-01 00:00:00	null	null
2019-02-01 00:00:00	null	null
2019-03-01 00:00:00	2	3
2019-04-01 00:00:00	null	null
2019-05-01 00:00:00	null	null
2019-06-01 00:00:00	10	2