orders has one row per delivery: order_id, city and order_date (text). For each city, and for every day from that city's first order to its last, find the number of orders and the 7-day rolling average of daily orders (that day and the six before it), rounded to 2 decimals. A day with no orders counts as 0 and still counts toward the window. Near the start, a window that does not have seven days yet averages the days it has. Return result with the columns city, order_date (YYYY-MM-DD), orders and rolling_7d, sorted by city, then date.
The data
orders22 rows
| order_idint64 | cityobject | order_dateobject |
|---|
| 9001 | Denver | 2025-06-07 |
| 9002 | Denver | 2025-06-07 |
| 9003 | Austin | 2025-06-01 |
| 9004 | Austin | 2025-06-02 |
| 9005 | Austin | 2025-06-05 |
| 9006 | Austin | 2025-06-09 |
and 16 more rows
Target output
| city | order_date | orders | rolling_7d |
|---|
| Austin | 2025-06-01 | 3 | 3 |
| Austin | 2025-06-02 | 1 | 2 |
| Austin | 2025-06-03 | 0 | 1.33 |
| Austin | 2025-06-04 | 2 | 1.5 |
| Austin | 2025-06-05 | 4 | 2 |
| Austin | 2025-06-06 | 0 | 1.67 |
| Austin | 2025-06-07 | 0 | 1.43 |
| Austin | 2025-06-08 | 1 | 1.14 |
| Austin | 2025-06-09 | 2 | 1.29 |
| Denver | 2025-06-02 | 2 | 2 |
| Denver | 2025-06-03 | 2 | 2 |
| Denver | 2025-06-04 | 0 | 1.33 |
| Denver | 2025-06-05 | 0 | 1 |
| Denver | 2025-06-06 | 0 | 0.8 |
| Denver | 2025-06-07 | 5 | 1.5 |
What this question tests
Tests the difference between rows and days. A rolling window over rows treats the day after a gap as if it were the day before, so a 7-day average built that way quietly covers 9 or 10 days. Filling the missing days with zeros first is the whole question.
More Python interview questions