Your question is Rolling 7-Day Metric With Windows. Start with the requirements and the three 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.
Amazon Seller Central needs a report showing which product categories generate the most completed order revenue for each seller.
Write a PostgreSQL query that joins sellers, categories, and orders to rank each seller's categories by completed order revenue during January 2025.
status = 'Completed' and an order_date from 2025-01-01 through 2025-01-31.SUM(order_amount).DENSE_RANK() to rank categories separately within each seller, with the highest revenue ranked first.category_rank <= 2 represent each seller's top two revenue ranks, including ties.| Column | Type | Description |
|---|---|---|
| seller_idPK | INT | Unique seller identifier |
| seller_name | VARCHAR(100) | Seller display name |
| Column | Type | Description |
|---|---|---|
| category_idPK | INT | Unique product category identifier |
| category_name | VARCHAR(100) | Product category name |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| seller_id | INT | References sellers.seller_id |
| category_id | INT | References categories.category_id |
| order_date | DATE | Date the order was placed |
| status | VARCHAR(20) | Order fulfillment status |
| order_amount | NUMERIC(12,2) | Revenue attributed to the order |