Your question is Top Products by Monthly Revenue. 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.
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 |