Workspace Seat Utilisation Status

Easy
402.

Workspace Seat Utilisation Status

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_limit when seats_active is greater than seats_purchased;
  • full when they are equal;
  • underused when fewer than half the seats are in use (seats_active * 2 < seats_purchased);
  • healthy otherwise.

Return workspace_id, workspace_name and seat_status for every workspace, in any order.

Tables

Table: workspaces

ColumnType
workspace_idint
workspace_namevarchar
seats_purchasedint
seats_activeint

Primary key: workspace_id.

One row per paying workspace; seats_active counts members who are not deactivated.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

workspaces

workspace_idworkspace_nameseats_purchasedseats_active
1Zentrix Labs2527
2Quillpad1010
3Ledgerly5012
4Orbitpay2010
5Vaani AI85
6Tiffinly4019

Output

workspace_idworkspace_nameseat_status
1Zentrix Labsover_limit
2Quillpadfull
3Ledgerlyunderused
4Orbitpayhealthy
5Vaani AIhealthy
6Tiffinlyunderused
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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_limit when seats_active is greater than seats_purchased;
  • full when they are equal;
  • underused when fewer than half the seats are in use (seats_active * 2 < seats_purchased);
  • healthy otherwise.

Return workspace_id, workspace_name and seat_status for every workspace, in any order.

Tables

Table: workspaces

ColumnType
workspace_idint
workspace_namevarchar
seats_purchasedint
seats_activeint

Primary key: workspace_id.

One row per paying workspace; seats_active counts members who are not deactivated.

Examples

Example 1

workspaces

workspace_idworkspace_nameseats_purchasedseats_active
1Zentrix Labs2527
2Quillpad1010
3Ledgerly5012
4Orbitpay2010
5Vaani AI85
6Tiffinly4019

Output

workspace_idworkspace_nameseat_status
1Zentrix Labsover_limit
2Quillpadfull
3Ledgerlyunderused
4Orbitpayhealthy
5Vaani AIhealthy
6Tiffinlyunderused

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 workspaces

Another 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 workspaces

Another 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 →