We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to split the event seasons into multiple smaller ones, ending a new season a week after an event has taken place. A new season will start the day after, again ending 7 days after the first event in this new season, up until the intial end date. I wish to create a table with a least of the new seasons, the new start and end dates, and an included counter in the new season's name.
The case for a single season in the dataset can be found here. If there is a gap at the end until the initial season_end, I can easily add it afterwards, but I'm struggling with multiple, overlapping seasons: my dataset contains multiple seasons with differing start and end dates, each overlapping, but they can be grouped by season_name. The single case linked above would not work when multiple seasons are present in the dataset, since it uses LIMIT, and combining it with a GROUP BY clause is not possible.
Example input:
id | season_name | season_start | season_end | event_date
1 | Festive | 2022-01-01 | 2022-01-31 | 2022-01-01
2 | Festive | 2022-01-01 | 2022-01-31 | 2022-01-02
3 | Festive | 2022-01-01 | 2022-01-31 | 2022-01-10
4 | Festive | 2022-01-01 | 2022-01-31 | 2022-01-16
5 | Festive | 2022-01-01 | 2022-01-31 | 2022-01-18
6 | Festive | 2022-01-01 | 2022-01-31 | 2022-01-20
7 | Joy | 2021-12-28 | 2022-02-05 | 2022-01-14
8 | Joy | 2021-12-28 | 2022-02-05 | 2022-01-15
9 | Joy | 2021-12-28 | 2022-02-05 | 2022-01-27
10 | Joy | 2021-12-28 | 2022-02-05 | 2022-01-28
Example output:
season_name | season_start | season_end | range_name | range_start | range_end
Festive | 2022-01-01 | 2022-01-31 | Festive-1 | 2022-01-01 | 2022-01-07
Festive | 2022-01-01 | 2022-01-31 | Festive-2 | 2022-01-08 | 2022-01-16
Festive | 2022-01-01 | 2022-01-31 | Festive-3 | 2022-01-17 | 2022-01-24
Festive | 2022-01-01 | 2022-01-31 | Festive-4 | 2022-01-25 | 2022-01-31
Joy | 2021-12-28 | 2022-02-05 | Joy-1 | 2021-12-28 | 2022-01-20
Joy | 2021-12-28 | 2022-02-05 | Joy-2 | 2022-01-21 | 2022-02-02
Joy | 2021-12-28 | 2022-02-05 | Joy-3 | 2022-02-03 | 2022-02-05
Here is the result of the case for the single season. As said before, the 'missing' final ranges (Festive-4 and Joy-3) are no problem, I can easily add those afterwards. But since the first step of the recursion looks at the top row (which happens to be season_name=Festive with id=1) and uses its end date, the first range for season_name=Joy starts at 2022-01-08, instead of at 2022-01-20.
I therefore would like to know if there is there a way to get this code to work when grouped by season_name?
