Calculate the average number of vacant days across all active listings in 2021. A vacant day is a day the listing was not held by a confirmed booking. Only include listings where is_active is true. Cap any check-in before 1 January 2021 to that date, and any check-out after 31 December 2021 to that date. Treat every listing as having 365 days in 2021. Return the average rounded to the nearest whole number.
Schema
listings
listing_idINTEGER
host_idINTEGER
is_activeBOOLEAN
cityVARCHAR
bookings
booking_idINTEGER
listing_idINTEGER
check_inDATE
check_outDATE
statusVARCHAR
status values: 'confirmed', 'cancelled'
Target output
What this question tests
Tests calendar-overlap arithmetic with boundary capping: bookings must be clamped to the edges of the year before occupied days are counted and inverted into vacancy. Date-range overlap is a genuinely hard interview family, and the capping step is where most attempts go wrong.
More SQL interview questions
