SQL bin all events into 3 month buckets

Question

I am trying to create a 3 month lookahead type table. So for example any event where LAST_DAY(event_date) = '2021-01-01' (i.e Jan-2021) will be valid in February-2021 and March-2021 as well.

For each event, I want another row for the 2 months after which it happens. Example below:

Given data that looks like this:

event_date	customer_id
Jan-21	A
Feb-21	B
Jan-21	A
Jun-21	C
Mar-21	D

I want to create some thing that looks like this:

event_date	customer_id
Jan-21	A
Feb-21	A
Mar-21	A
Feb-21	B
Mar-21	B
Apr-21	B
Jun-21	C
Jul-21	C
Aug-21	C
Mar-21	D
Apr-21	D
May-21	D

Kind of stuck on how to achieve this. You can assume that the event_date is a valid datetime field here. I've tried a few different things to no avail. I would usually use a window function for something like this with PRECEDING and FOLLOWING limits here but LEAD, LAG type functions only allow you to set 1 offset and not a range of offset.

Any help is appreciated. Working in PSQL.

user_1566187 · Answer

You could do this in two steps:

Group the data (e.g. A appears twice, but you want to compute that only once since both rows of A are in this case identical)
Generate the new data with additional rows for next month and the month after that

Step one is simple given your example, in a more elaborate situation you might group data with group by, here a distinct is sufficient:

select distinct event_date, customer_id from table;

This is the basis and the other two generated information per row rely on this data. Maybe you could create a view for this and reuse it. I will do so and call it view, but you could as well simply use the SQL statement as is. Think of it as view is equal to select distinct event_date, customer_id from table.

The second step can be done by adding the data for next month, and similarly the data the month after that. A union comes to mind.

select event_date, customer_id from view
UNION 
select next_month(event_date), customer_id from view
UNION
select after_next_month(event_date), customer_id from view

Now we are left with date arithmetic, you want to calculate the next month and the one after that.

If that is ok for you I would suggest you merely add 30 to the date, e.g. select event_date + 30, customer_id from view and select event_date + 60, customer_id from view.

Otherwise it should not be too difficult to do the appropriate date arithmetic, see Date/Time Functions and Operators.

user_1863229 · Answer

We can use a calendar table approach here:

WITH months AS (
    SELECT date_trunc('day', dd)::date AS month
    FROM generate_series ('2021-01-01'::timestamp,
                          '2021-12-01'::timestamp,
                          '1 month'::interval) dd
)

SELECT DISTINCT m.month, t.customer_id
FROM months m
INNER JOIN yourTable t
    ON m.month BETWEEN t.event_date AND t.event_date + interval '2 month'
ORDER BY
    t.customer_id, m.month;

Demo

The idea is to join the calendar table of all dates to your table of events on the condition that the months match, or the former's month be 1 or 2 months later. Note that I assume that each "month" can be represented by the full date at the first of the month. You should ideally always be working with proper dates here.

SQL bin all events into 3 month buckets

2 Answers

Demo