Your question is Top Products Rolling Window SQL. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Bain & Company's product analytics team uses Bain Insights to monitor recent e-commerce performance. Write a PostgreSQL query that identifies the highest-grossing products in each category over a rolling 30-day period ending on 2025-02-15.
order_items to products and calculate gross revenue as quantity * unit_price.category, product_name, gross_revenue, and the rank, ordered by category and rank.| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Unique product identifier |
| product_name | VARCHAR(100) | Product name |
| category | VARCHAR(50) | Product category |
| Column | Type | Description |
|---|---|---|
| order_item_idPK | INT | Unique order line identifier |
| product_id | INT | Referenced product identifier |
| order_date | DATE | Date on which the item was purchased |
| quantity | INT | Number of units purchased |
| unit_price | DECIMAL(10,2) | Price per purchased unit |