Workspace Seat Utilisation Status — SQL Problem
A seat-based collaboration tool sells each workspace a number of seats.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #402
Problem statement
A seat-based collaboration tool sells each workspace a number of seats. Customer success labels every workspace by how its active members compare with its purchased seats:
over_limitwhenseats_activeis greater thanseats_purchased;fullwhen they are equal;underusedwhen fewer than half the seats are in use (seats_active * 2 < seats_purchased);healthyotherwise.
Return workspace_id, workspace_name and seat_status for every workspace, in any order.
Tables
Table: workspaces
| Column | Type |
|---|---|
| workspace_id | int |
| workspace_name | varchar |
| seats_purchased | int |
| seats_active | int |
Primary key: workspace_id.
One row per paying workspace; seats_active counts members who are not deactivated.
Examples
Example 1
workspaces
| workspace_id | workspace_name | seats_purchased | seats_active |
|---|---|---|---|
| 1 | Zentrix Labs | 25 | 27 |
| 2 | Quillpad | 10 | 10 |
| 3 | Ledgerly | 50 | 12 |
| 4 | Orbitpay | 20 | 10 |
| 5 | Vaani AI | 8 | 5 |
| 6 | Tiffinly | 40 | 19 |
Output
| workspace_id | workspace_name | seat_status |
|---|---|---|
| 1 | Zentrix Labs | over_limit |
| 2 | Quillpad | full |
| 3 | Ledgerly | underused |
| 4 | Orbitpay | healthy |
| 5 | Vaani AI | healthy |
| 6 | Tiffinly | underused |
How to solve Workspace Seat Utilisation Status
Every workspace gets exactly one label, computed from its own row, so this is a CASE in the select list with no grouping or joining.
The branches are tested in order and the first true one wins. Testing over_limit and full first means the underused branch only ever sees workspaces with spare seats, and healthy is whatever is left. Writing the half-rule as seats_active * 2 < seats_purchased keeps the arithmetic in integers, so 10 of 20 seats (exactly half) is healthy and 9 of 20 is underused — no rounding question arises.
A nested IF is the MySQL shorthand for the same chain. The UNION ALL version spells each label as its own filter; it is correct only because the four conditions are made mutually exclusive by hand, which is exactly the bookkeeping CASE does for you. All are a single scan.
Reference solution (MySQL)
SELECT workspace_id, workspace_name,
CASE
WHEN seats_active > seats_purchased THEN 'over_limit'
WHEN seats_active = seats_purchased THEN 'full'
WHEN seats_active * 2 < seats_purchased THEN 'underused'
ELSE 'healthy'
END AS seat_status
FROM workspacesAnother way
SELECT workspace_id, workspace_name, IF(seats_active > seats_purchased, 'over_limit', IF(seats_active = seats_purchased, 'full', IF(seats_active * 2 < seats_purchased, 'underused', 'healthy'))) AS seat_status FROM workspacesAnother way
SELECT workspace_id, workspace_name, 'over_limit' AS seat_status FROM workspaces WHERE seats_active > seats_purchased UNION ALL SELECT workspace_id, workspace_name, 'full' FROM workspaces WHERE seats_active = seats_purchased UNION ALL SELECT workspace_id, workspace_name, 'underused' FROM workspaces WHERE seats_active * 2 < seats_purchased UNION ALL SELECT workspace_id, workspace_name, 'healthy' FROM workspaces WHERE seats_active < seats_purchased AND seats_active * 2 >= seats_purchased← Signups by Work Email Domain · Active Subscriptions Above the Average MRR →