Your question is SQL CTE for Top 3 Products. 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.
Freddie Mac wants to compare revenue generated by offerings such as Loan Product Advisor and Home Possible. Write a PostgreSQL query using CTEs to return the top three products by revenue from completed orders.
order_status = 'completed'.quantity * unit_price, aggregate it by product, and rank products with a window function.product_id as a deterministic tie-breaker.| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Unique product identifier |
| product_name | VARCHAR(100) | Freddie Mac product name |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| order_status | VARCHAR(20) | Current order status |
| Column | Type | Description |
|---|---|---|
| order_id | INT | Identifier of the related order |
| product_id | INT | Identifier of the purchased product |
| quantity | INT | Number of units purchased |
| unit_price | NUMERIC(10,2) | Price for one unit |