'SQL Calculate field based on three rows
How could I calculate a field based on values from previous and next rows?
I have this list from users with a date (month and year) and a field indicating if the user has 1+ purchases in that month-year
| id_user | Date | Has_purchases | Active |
|---|---|---|---|
| 15678 | Jan 2021 | 0 | 1 |
| 15678 | feb 2021 | 1 | 1 |
| 15678 | mar 2021 | 0 | 1 |
| 15678 | Apr 2021 | 0 | 1 |
| 15678 | may 2021 | 0 | 0 |
| 15678 | jun 2021 | 0 | 1 |
| 15678 | jul 2021 | 0 | 1 |
| 15678 | Aug 2021 | 1 | 1 |
| 15678 | sep 2021 | 0 | 1 |
| 15678 | oct 2021 | 0 | 1 |
| 15678 | nov 2021 | 0 | 1 |
| 15678 | Dec 2021 | 1 | 1 |
I need to calculate if the user was active on a date (month-year). An active user is defined as an user who has at least one purchase on the last 3 months.
Eg. User 15678 is 'active' on march because user has purchases on february, the same user in unactive on may beacause it does not have purchases on march and april and also does not have purchases on june and july
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|