sql - PostgreSQL counting from a special date

Question

i have the following code,

SELECT      
    years_month_count.day_date,
    years_month_count.year_date,
    years_month_count.month_date,
    years_month_count.no_of_customers_day,
    sum(years_month_count.no_of_customers_day) OVER (PARTITION BY year_date ORDER BY day_date) AS no_of_customers_ytd
FROM (
    SELECT 
        DATE(date) as day_date,
        DATE_PART('year',date) as year_date,
        DATE_PART('month',date) as month_date,
        count(prepare_first_buyer.person_id) as no_of_customers_day
    FROM (
        SELECT 
            DATE(bestelldatum),
            person_id,
            ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id)
        FROM ani.bestellung
    ) prepare_first_buyer
    WHERE row_number=1
    GROUP BY DATE(date), DATE_PART('year',date),DATE_PART('month',date)
    ORDER BY DATE(date), DATE_PART('year',date),DATE_PART('month',date)
) years_month_count

the Output looks like this:

day_date	year_date	month_date	no_of_customers_day	no_of_Customers_ytd
2017-04-04	2017	4	6	6
2017-04-05	2017	4	4	10
...	...	...	...	...
...	...	...	...	...

Answer 1

step-by-step demo:db<>fiddle

SELECT
    *,
    SUM(value) OVER (PARTITION BY                             -- 4
        date_part('year',                                     -- 3
            the_date - interval '5 months'                    -- 2
        )     
    )
FROM t
WHERE date_part('month', the_date)::int NOT BETWEEN 4 AND 5   -- 1

1. Filter all dates you are not requiring. In your example all dates with months 4 and 5
2. Shift your date range start to the beginning of the year. In your example you have to shift: year-06-01 to year-01-01, so you need to subtract 5 months. Because your date range never exceeds a year, all your relevant data now has the same year, which makes a great group criterion
3. Extract the year part to use it as group/partition criterion
4. Do your calculation on this criterion