Compute 3-Day Rolling Revenue Averages with Pandas

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.

Pivot 3-Row Rolling Revenue and Total Units by Product

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.

Table sales

column	type	constraints
date	DATE	NOT NULL
product_id	INTEGER	NOT NULL
units	INTEGER	NOT NULL
revenue	DECIMAL(10,2)	NOT NULL

Sample data:

date	product_id	revenue	units
2023-01-01	101	100	5
2023-01-02	101	60	3
2023-01-03	102	160	8
2023-01-04	101	40	2
2023-01-05	102	140	7

Expected output

Pivoted table with one row per date. For each product_id, show its 3-row rolling average revenue in a dedicated column, and its total units sold across all dates in additional columns. Rolling averages are rounded to 2 decimals; cells are NULL where a product has no sales up to that date.

date	rev_101	rev_102	total_units_101	total_units_102
2023-01-01	100	NULL	10	15
2023-01-02	80	NULL	10	15
2023-01-03	NULL	160	10	15
2023-01-04	66.67	NULL	10	15
2023-01-05	NULL	150	10	15

Hints

1. Use a window function with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to compute the 3-row rolling average per product_id.
2. Use another window SUM over units partitioned by product_id to get total units per product.
3. Pivot the results by date with conditional aggregation (CASE WHEN product_id = ... THEN ... END) and GROUP BY date.