Write a SQL query to analyze workspace utilization by location and month, joining bookings, members, and locations tables to identify underperforming WeWork sites.
Use confirmed bookings made by active members. Define utilization as booked hours divided by available hours on days with confirmed bookings, assuming 8 available hours per desk per day. A site is underperforming when utilization is below 50%.
location_name, month, booked_hours, utilization_pct, and underperforming.month, then location_name.| Column | Type | Description |
|---|---|---|
| booking_idPK | INT | Unique booking identifier |
| location_id | INT | WeWork location associated with the booking |
| member_id | INT | Member who made the booking |
| booking_date | DATE | Date of the booking |
| booking_hours | DECIMAL(6,2) | Number of workspace hours booked |
| booking_status | VARCHAR(20) | Booking state, such as confirmed or canceled |
| Column | Type | Description |
|---|---|---|
| member_idPK | INT | Unique member identifier |
| member_name | VARCHAR(100) | Member full name |
| membership_status | VARCHAR(20) | Current membership status |
| Column | Type | Description |
|---|---|---|
| location_idPK | INT | Unique WeWork location identifier |
| location_name | VARCHAR(100) | WeWork location name |
| desk_capacity | INT | Number of available desks |