Seat-Based Monthly Bill for Every Workspace

Medium
415.

Seat-Based Monthly Bill for Every Workspace

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

ColumnType
plan_codevarchar
price_per_seatint
min_seatsint

Primary key: plan_code.

Price per seat per month in rupees, and the seats always billed.

Table: workspaces

ColumnType
workspace_idint
workspace_namevarchar
plan_codevarchar

Primary key: workspace_id.

plan_code always names a row of plans.

Table: members

ColumnTypeValues
member_idint
workspace_idint
roleenumowner, admin, member, guest
deactivated_ondate

Primary key: member_id.

One row per person ever added to a workspace; deactivated_on is NULL while they still have access.

Example

The rows must come back in the order shown.

plans

plan_codeprice_per_seatmin_seats
team3993
business6995
enterprise99920

workspaces

workspace_idworkspace_nameplan_code
1Zentrix Labsteam
2Quillpadbusiness
3Ledgerlyteam
4Orbitpayenterprise

members

member_idworkspace_idroledeactivated_on
11ownerNULL
21adminNULL
31memberNULL
41memberNULL
51guestNULL
61member2024-05-31
72ownerNULL
82memberNULL
92memberNULL
103ownerNULL
1 more row

Output

workspace_idworkspace_namebillable_membersbilled_seatsamount_due
1Zentrix Labs441596
2Quillpad353495
3Ledgerly131197
4Orbitpay02019980
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.

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

ColumnType
plan_codevarchar
price_per_seatint
min_seatsint

Primary key: plan_code.

Price per seat per month in rupees, and the seats always billed.

Table: workspaces

ColumnType
workspace_idint
workspace_namevarchar
plan_codevarchar

Primary key: workspace_id.

plan_code always names a row of plans.

Table: members

ColumnType
member_idint
workspace_idint
roleenum(owner, admin, member, guest)
deactivated_ondate

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_codeprice_per_seatmin_seats
team3993
business6995
enterprise99920

workspaces

workspace_idworkspace_nameplan_code
1Zentrix Labsteam
2Quillpadbusiness
3Ledgerlyteam
4Orbitpayenterprise

members

member_idworkspace_idroledeactivated_on
11ownerNULL
21adminNULL
31memberNULL
41memberNULL
51guestNULL
61member2024-05-31
72ownerNULL
82memberNULL
92memberNULL
103ownerNULL
113guestNULL

Output

workspace_idworkspace_namebillable_membersbilled_seatsamount_due
1Zentrix Labs441596
2Quillpad353495
3Ledgerly131197
4Orbitpay02019980

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_id

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

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