Compute 3-Day Rolling Revenue Averages with Pandas
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
sales
+------------+-----------+-------+---------+
| date | product_id| units | revenue |
+------------+-----------+-------+---------+
| 2023-01-01 | 101 | 5 | 100.0 |
| 2023-01-02 | 101 | 3 | 60.0 |
| 2023-01-03 | 102 | 8 | 160.0 |
| 2023-01-04 | 101 | 2 | 40.0 |
| 2023-01-05 | 102 | 7 | 140.0 |
+------------+-----------+-------+---------+
##### Scenario
Cleaning and aggregating daily sales data to compute rolling revenue trends per product.
##### Question
Using Python/pandas on the sales table, compute the 3-day rolling average of revenue for each product_id, pivot the result so dates are rows and product_ids are columns, and include total units sold per product.
##### Hints
Show imports, set date index, sort, groupby+rolling, pivot, merge totals; code should run end-to-end.
Overview: This question evaluates proficiency with Python pandas for time-series data manipulation, specifically group-wise aggregation, rolling-window calculations, pivoting, and merging summary statistics.
Given the daily sales data, compute the 3-row rolling average of revenue for each product_id (using the current row and up to the 2 preceding rows within the same product, ordered by date). Then pivot the result so that each date is a row and each product_id is a separate column showing its rolling average. Additionally, include the total units sold per product_id as extra columns in the same result.
Tables
sales(date DATE, product_id INTEGER, units INTEGER, revenue DECIMAL(10,2))
Hints
- Use a window function with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to compute the 3-row rolling average per product_id.
- Use another window SUM over units partitioned by product_id to get total units per product.