Tell me how you would approach writing SQL for a task that requires interpreting an ERD and producing results from subqueries with calculations and conditions under tight time constraints.
For this exercise, use the provided property, booking, and payment tables. Produce the requested result while making assumptions explicit and handling missing payment records.
Output
- One row per qualifying property and booking month.
- Columns:
property_name, booking_month, completed_bookings, net_revenue, average_booking_value, previous_month_revenue, revenue_change_pct, and revenue_rank.
- Include only months with at least two positive-value completed bookings.
- Order by booking month ascending, revenue rank ascending, and property name ascending.
Output details
- Use the first day of each booking's check-in month as
booking_month.
- Include only bookings with
booking_status equal to completed and total_amount greater than 0.
- Calculate
net_revenue as settled amounts plus, refunded amounts minus, and all other payment statuses zero; treat missing payments as 0.
- Round
average_booking_value and revenue_change_pct to two decimal places, leaving net_revenue unrounded.
- Use the prior qualifying month for the same property as
previous_month_revenue, and return NULL for missing or zero prior revenue and its percentage change.
- Assign dense revenue ranks within each month by descending
net_revenue, giving tied values the same rank.
