Given this table containing buying information of customer_id "xxxxxx". How can we write a script that computes the column nb_bmw_cars that are to the number of rows where car_type is "BMW" BUT in the interval ] date - 365 days: date - 1 day [ for each date. (in sqlalchemy). customer_id date car_type nb_bmw_cars xxxxxx 2022-11-22 BMW - xxxxxx 2023-08-24 BMW 1 xxxxxx 2023-12-21 BMW 2 xxxxxx 2023-12-21 BMW 2 xxxxxx 2023-12-21 MERCEDES 2 xxxxxx 2023-12-21 BMW 2 xxxxxx 2023-12-21 BMW 2 xxxxxx 2024-02-07 BMW 6 xxxxxx 2024-02-07 BMW 6 xxxxxx 2024-02-07 BMW 6 xxxxxx 2024-10-31 BMW 9 xxxxxx 2024-10-31 BMW 9 xxxxxx 2024-10-31 BMW 9 To understand what is nb_bmw_cars, if you take for example "2024-10-31", it has 9 because there is 9 transactions of car_type BMW before the date (2024-10-31) . Continue reading...
