Seat-Based Monthly Bill for Every Workspace — SQL Problem
The collaboration tool bills per seat. A member is billable when they are not a guest (guests are free) and have not been deactivated (deactivated_on is…
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #415
Problem statement
The collaboration tool bills per seat. A member is billable when they are not a guest (guests are free) and have not been deactivated (deactivated_on is NULL). Each plan has a minimum number of seats that is always charged, so billed_seats is the larger of the billable members and the plan's min_seats, and amount_due = billed_seats × price_per_seat.
Return workspace_id, workspace_name, billable_members, billed_seats and amount_due for every workspace, including one with no members, ordered by workspace_id.
Tables
Table: plans
| Column | Type |
|---|---|
| plan_code | varchar |
| price_per_seat | int |
| min_seats | int |
Primary key: plan_code.
Price per seat per month in rupees, and the seats always billed.
Table: workspaces
| Column | Type |
|---|---|
| workspace_id | int |
| workspace_name | varchar |
| plan_code | varchar |
Primary key: workspace_id.
plan_code always names a row of plans.
Table: members
| Column | Type |
|---|---|
| member_id | int |
| workspace_id | int |
| role | enum(owner, admin, member, guest) |
| deactivated_on | date |
Primary key: member_id.
One row per person ever added to a workspace; deactivated_on is NULL while they still have access.
Examples
Example 1
plans
| plan_code | price_per_seat | min_seats |
|---|---|---|
| team | 399 | 3 |
| business | 699 | 5 |
| enterprise | 999 | 20 |
workspaces
| workspace_id | workspace_name | plan_code |
|---|---|---|
| 1 | Zentrix Labs | team |
| 2 | Quillpad | business |
| 3 | Ledgerly | team |
| 4 | Orbitpay | enterprise |
members
| member_id | workspace_id | role | deactivated_on |
|---|---|---|---|
| 1 | 1 | owner | NULL |
| 2 | 1 | admin | NULL |
| 3 | 1 | member | NULL |
| 4 | 1 | member | NULL |
| 5 | 1 | guest | NULL |
| 6 | 1 | member | 2024-05-31 |
| 7 | 2 | owner | NULL |
| 8 | 2 | member | NULL |
| 9 | 2 | member | NULL |
| 10 | 3 | owner | NULL |
| 11 | 3 | guest | NULL |
Output
| workspace_id | workspace_name | billable_members | billed_seats | amount_due |
|---|---|---|---|---|
| 1 | Zentrix Labs | 4 | 4 | 1596 |
| 2 | Quillpad | 3 | 5 | 3495 |
| 3 | Ledgerly | 1 | 3 | 1197 |
| 4 | Orbitpay | 0 | 20 | 19980 |
How to solve Seat-Based Monthly Bill for Every Workspace
Join each workspace to its plan (an inner join: every workspace has one), then LEFT JOIN only its billable members — the role and deactivation tests go in the ON clause. A workspace with no billable member then keeps one NULL-padded row, and COUNT(m.member_id) counts it as 0.
Had the conditions gone in WHERE, Orbitpay (no members) would reach the filter as a NULL-padded row, and m.role <> 'guest' on a NULL role is unknown, so the row would be dropped — losing a workspace that still owes its minimum. A workspace whose members were all guests or deactivated would vanish the same way. Keeping filters of the optional side in ON is the habit that avoids this.
The billed seats are GREATEST(billable, min_seats); a CASE does the same. The plan's columns are in GROUP BY because they are selected outside an aggregate. The third version LEFT JOINs every member and counts billable ones with a conditional SUM — then a member-less workspace's single NULL row sums to 0 because the CASE yields 0 for it. Each is one pass over members.
Reference solution (MySQL)
SELECT w.workspace_id, w.workspace_name,
COUNT(m.member_id) AS billable_members,
GREATEST(COUNT(m.member_id), p.min_seats) AS billed_seats,
GREATEST(COUNT(m.member_id), p.min_seats) * p.price_per_seat AS amount_due
FROM workspaces w
JOIN plans p ON p.plan_code = w.plan_code
LEFT JOIN members m
ON m.workspace_id = w.workspace_id AND m.role <> 'guest' AND m.deactivated_on IS NULL
GROUP BY w.workspace_id, w.workspace_name, p.min_seats, p.price_per_seat
ORDER BY w.workspace_idAnother way
SELECT workspace_id, workspace_name, billable_members,
CASE WHEN billable_members > min_seats THEN billable_members ELSE min_seats END AS billed_seats,
CASE WHEN billable_members > min_seats THEN billable_members ELSE min_seats END * price_per_seat AS amount_due
FROM (
SELECT w.workspace_id, w.workspace_name, p.min_seats, p.price_per_seat,
(SELECT COUNT(*) FROM members m WHERE m.workspace_id = w.workspace_id AND m.role <> 'guest' AND m.deactivated_on IS NULL) AS billable_members
FROM workspaces w JOIN plans p ON p.plan_code = w.plan_code
) t
ORDER BY workspace_idAnother way
SELECT w.workspace_id, w.workspace_name,
SUM(CASE WHEN m.role <> 'guest' AND m.deactivated_on IS NULL THEN 1 ELSE 0 END) AS billable_members,
GREATEST(SUM(CASE WHEN m.role <> 'guest' AND m.deactivated_on IS NULL THEN 1 ELSE 0 END), p.min_seats) AS billed_seats,
GREATEST(SUM(CASE WHEN m.role <> 'guest' AND m.deactivated_on IS NULL THEN 1 ELSE 0 END), p.min_seats) * p.price_per_seat AS amount_due
FROM workspaces w JOIN plans p ON p.plan_code = w.plan_code
LEFT JOIN members m ON m.workspace_id = w.workspace_id
GROUP BY w.workspace_id, w.workspace_name, p.min_seats, p.price_per_seat
HAVING COUNT(*) > 0
ORDER BY w.workspace_id← Upgrades, Downgrades and Lateral Plan Moves · User Sessions From Thirty-Minute Event Gaps →