Hello
I have a table with all my orders, linked to customers with a date and a channel
Here's a sample
| CustomerID | OrderID | Date | Channel |
| A1 | 1 | 01/05/2023 | M |
| A1 | 2 | 31/03/2022 | M |
| A1 | 3 | 19/12/2022 | G |
| A2 | 4 | 19/07/2023 | G |
| A2 | 5 | 01/07/2020 | G |
| A3 | 6 | 01/06/2023 | M |
| A4 | 7 | 28/02/2021 | M |
| A4 | 8 | 15/12/2020 | G |
| A4 | 9 | 20/05/2022 | G |
| A4 | 10 | 15/07/2021 | G |
| A4 | 11 | 07/07/2023 | G |
| A4 | 12 | 03/04/2021 | M |
I'd like to create a new table that calculate number of orders per year per customer for a specific channel.
Let's take the example of channel G, I'd like to create the following table
| CustomerID | N | N+1 | N+2 | N+3 | N+4 |
| A1 | 1 | 1 | | | |
| A2 | 1 | | | | 1 |
| A4 | 1 | | 1 | 2 | |
N = Number of orders in the last 365 days of the customer for the channel G
N+1 = Number of orders between 366 and 730 days compare to today of the customer for the channel G
I guess it is not that complicated as it seems something classical to do, but I don't find the right way to do it.
Thanks for your help
Maxime
