How to count records before current record date?

Question

I have a table like this:

-----------------
id | date
-----------------
1  | 2023-10-07
2  | 2023-10-08
3  | 2023-10-14

and I want to get the record count up to the current record where the date is less than Sunday of the current record. So I want the output to look like this:

---------------------
week_number | count
---------------------
2           | 2
3           | 3

I tried different approaches, but the result is a counter for entries within the week. It’s as if he doesn’t look at all records before the current one, but only at those that are in the same week.

WITH weekly AS (
    SELECT
        get_week_number(date::DATE) AS week_number,
        COUNT(*) AS count
    FROM
        inventory
    WHERE
        date <= date_trunc('week', date)::date + INTERVAL '6 days'
    GROUP BY
        week_number
)

SELECT
    week_number, count
FROM
    inventory
        JOIN weekly ON get_week_number(date::DATE) = weekly.week_number
WHERE
    date <= date_trunc('week', date)::date + INTERVAL '6 days'
GROUP BY
    week_number, count
ORDER BY
    week_number;

Answer 1

This computes

the record count up to the current record where the date is less than Sunday of the current record

SELECT *
     , extract(week FROM date + 1) AS week_nr  -- 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;

I assume by "Sunday of the current record" you mean weeks starting on Sunday. ISO weeks start on Monday. date_trunc() truncates weeks accordingly. Use date_bin() to "truncate" to Sunday. See:

• Make date_trunc() start on Sunday instead of Monday

Add a day to your date before extracting the week number to fix the same off-by-1 error. (There may be corner-case issues around the turn of the year.)

To get the count of rows with a "date is less than Sunday of the current record", you have to stop the count at "last week". RANGE BETWEEN UNBOUNDED PRECEDING AND '1 day' PRECEDING achieves that, after reducing every date to the Sunday of the week.
Details in the manual about window functions.

To 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;

Note that a week number without year results in confusing or wrong aggregations quickly. So I based counts on full dates and display the year additionally. (No off-by-1 there.)

fiddle