Rolling 7-Day Metric With Windows

Business Context

Amazon Seller Central needs a report showing which product categories generate the most completed order revenue for each seller.

Task

Write a PostgreSQL query that joins sellers, categories, and orders to rank each seller's categories by completed order revenue during January 2025.

Requirements

1. Include only orders with status = 'Completed' and an order_date from 2025-01-01 through 2025-01-31.
2. Calculate total revenue for each seller and category using SUM(order_amount).
3. Use DENSE_RANK() to rank categories separately within each seller, with the highest revenue ranked first.
4. Return the seller, category, total revenue, and category rank. Sort results by seller and rank. Rows with category_rank <= 2 represent each seller's top two revenue ranks, including ties.