I have a data set that looks like below
| customer_id | date | order_id | timestamp |
|---|---|---|---|
| 1 | 2024-01-01 | xy | 2024-01-01 18:47:33 |
| 1 | 2024-01-01 | zr | 2024-01-01 20:15:00 |
| 1 | 2024-01-06 | df | |
| 2 | 2023-12-01 | mn | |
| 2 | 2024-01-05 | cv | |
| 3 | 2024-01-04 | ef |
I want a window function that for each order id, it provides me the customer's +/- 7 days of order ids (excluding the current row). If the customer has no other order within the +/- 7 days window, then it gives me only 1 row with a null in the orders column. Currently I am using self-join but it is too slow
| customer_id | date | order_id | orders |
|---|---|---|---|
| 1 | 2024-01-01 | xy | zr |
| 1 | 2024-01-01 | xy | df |
| 1 | 2024-01-01 | zr | xy |
| 1 | 2024-01-01 | zr | df |
| 1 | 2024-01-06 | df | xy |
| 1 | 2024-01-06 | df | zr |
| 2 | 2023-12-01 | mn | NULL |
| 2 | 2024-01-05 | cv | NULL |