Write a query to find the second-highest revenue-generating property in each market using a window function.
Use the markets, properties, and bookings tables. Include confirmed and completed bookings, exclude cancelled bookings, and treat tied second-highest revenues as valid results.
market_name, property_id, property_name, and total_revenuemarket_name, then property_id| Column | Type | Description |
|---|---|---|
| market_idPK | INT | Unique market identifier |
| market_name | VARCHAR(100) | Market name |
| Column | Type | Description |
|---|---|---|
| property_idPK | INT | Unique property identifier |
| market_id | INT | Market containing the property |
| property_name | VARCHAR(150) | Property display name |
| Column | Type | Description |
|---|---|---|
| booking_idPK | INT | Unique booking identifier |
| property_id | INT | Booked property |
| status | VARCHAR(30) | Booking status |
| revenue | DECIMAL(12,2) | Revenue attributed to the booking |