Wealthfront wants a monthly view of which products generate the most net revenue. Write a PostgreSQL query using layered CTEs and a window function to identify the top three products for each month.
COALESCE so nullable transaction amounts do not produce unexpected totals.ROW_NUMBER() partitioned by month and ordered by revenue descending, with product_id as a deterministic tie-breaker.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INTEGER | Unique transaction identifier |
| product_id | INTEGER | Referenced Wealthfront product |
| occurred_at | TIMESTAMP | Transaction timestamp |
| amount | NUMERIC(12,2) | Signed sale or refund amount |
| transaction_type | VARCHAR(20) | Transaction classification |
| status | VARCHAR(20) | Processing status |
| Column | Type | Description |
|---|---|---|
| product_idPK | INTEGER | Unique product identifier |
| product_name | VARCHAR(100) | Wealthfront product name |
| category_id | INTEGER | Referenced product category |
| Column | Type | Description |
|---|---|---|
| category_idPK | INTEGER | Unique category identifier |
| category_name | VARCHAR(100) | Product category name |