Iterate through data and calculate the sum of the last three days of each month

Question

DB-Fiddle

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    sales_volume INT
);

INSERT INTO sales
(event_date, sales_volume)
VALUES 
('2023-01-27', '900'),
('2023-02-28', '500'),
('2023-01-29', '100'),
('2023-01-30', '600'),
('2023-01-31', '450'),
  
('2023-02-25', '300'),
('2023-02-26', '250'),
('2023-02-27', '845'),
('2023-02-28', '520'), 

('2023-03-26', '750'),
('2023-03-27', '750'),
('2023-03-28', '625'),
('2023-03-29', '885'),
('2023-03-30', '120'),
('2023-03-31', '400');

---

Expected Result:

Row	event_time	sales_volume
1	1	1150
2	2	2115
3	3	1405

---

I want to iterate through the table and calculate the sum of the sales_volume for each month.
Currently, I am able to achieve this using this query:

SELECT
DATE_PART('month', s.event_date) AS event_time,
SUM(s.sales_volume) AS sales_volume
FROM sales s
WHERE s.event_date BETWEEN '2023-01-29' AND '2023-01-31'
OR s.event_date BETWEEN '2023-02-26' AND '2023-02-28'
OR s.event_date BETWEEN '2023-03-29' AND '2023-03-31'
GROUP BY 1
ORDER BY 1;

However, my original database is much bigger than the example so I would need a lot of these OR-conditions.

How do I need to modify the query so it iterates through the table without using the OR-conditions?

Answer 1

demo:db<>fiddle

SELECT
    date_trunc('month', event_date) as month,
    SUM(sales_volume)
FROM (
    SELECT
        *,
        row_number() OVER (              -- 1
            PARTITION BY date_trunc('month', event_date) 
            ORDER BY event_date DESC
        )
    FROM sales 
) s
WHERE row_number <= 3                    -- 2
GROUP BY 1                               -- 3

1. Use row_number() window function to enumerate your days of month by descending day order. So last day gets number 1, last but one day number 2 and so on.
2. Filter records with row_number values <= 3, which represent the last three days of the month.
3. Group these records by the the month calculated by the date_trunc() function