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 |