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.
Output
- One row per qualifying property
- Columns:
market_name, property_id, property_name, and total_revenue
- Sort by
market_name, then property_id
Output details
- Consider only bookings with status exactly
confirmed or completed; exclude every other status.
- Include properties with no qualifying bookings and report their
total_revenue as numeric 0.
- Return
total_revenue as the unrounded sum of revenues, preserving full numeric precision.
- Sort
market_name ascending, then property_id ascending.
