Postgres 16

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 inventory (id int, date date);
INSERT INTO inventory VALUES
  (1, '2023-10-07')
, (2, '2023-10-08')
, (3, '2023-10-14')
, (4, '2023-10-22')
;

CREATE TABLE



INSERT 0 4


-- daily result
SELECT *
     , extract(week FROM date + 1) AS week_number  -- fix off-by-1
     , count(*) OVER (ORDER BY date_bin('7 days', date, timestamp '2023-1-1')
                      RANGE BETWEEN UNBOUNDED PRECEDING AND '1 day' PRECEDING) AS count_til_last_week
FROM   inventory;


id
date
week_number
count_til_last_week


1
2023-10-07
40
0

2
2023-10-08
41
1

3
2023-10-14
41
1

4
2023-10-22
43
3



SELECT 4


-- aggregate per week               
SELECT extract(year FROM sun) AS year
     , extract(week FROM sun + interval '1 day') AS week_number
     , sum(row_count) OVER (ORDER BY sun
                            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS count_til_last_week
FROM  (
   SELECT date_bin('7 days', date, timestamp '2023-1-1') AS sun
        , count(*) AS row_count
   FROM   inventory
   GROUP  BY 1
   ) sub;


year
week_number
count_til_last_week


2023
40
null

2023
41
1

2023
43
3



SELECT 3