Google: Prorated Workspace Seat-Days for June 2025
Google Workspace bills flexible plans by the day. Each customer's seat count is recorded only when it changes: from a change's effective_date the customer…
- Difficulty: Hard
- Topics: Window Functions, Dates, Joins
- Dialect: MySQL
- Problem: #694
Problem statement
Google Workspace bills flexible plans by the day. Each customer's seat count is recorded only when it changes: from a change's effective_date the customer holds seats seats until the day before its next change (or indefinitely if there is none). A change to 0 seats is a cancellation.
For June 2025 (2025-06-01 to 2025-06-30 inclusive), a customer's seat-days are the sum over every day of June of the seats held that day; days before the first change hold no seats. Return domain and seat_days for every customer with more than 0 seat-days, ordered by seat_days descending, then domain ascending.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: WorkspaceCustomer
| Column | Type |
|---|---|
| customer_id | int |
| domain | varchar |
Primary key: customer_id.
One Workspace customer; domain is unique.
Table: SeatChange
| Column | Type |
|---|---|
| customer_id | int |
| effective_date | date |
| seats | int |
Primary key: customer_id, effective_date.
The seat count a customer holds from effective_date on, until its next change.
Examples
Example 1
WorkspaceCustomer
| customer_id | domain |
|---|---|
| 1 | zentrix.io |
| 2 | quillpad.in |
| 3 | tiffinly.co |
| 4 | ledgerly.com |
| 5 | orbitpay.in |
SeatChange
| customer_id | effective_date | seats |
|---|---|---|
| 1 | 2025-04-10 | 20 |
| 1 | 2025-06-11 | 25 |
| 2 | 2025-06-20 | 10 |
| 3 | 2025-05-01 | 15 |
| 3 | 2025-06-16 | 0 |
| 4 | 2025-07-01 | 50 |
| 5 | 2025-03-01 | 5 |
| 5 | 2025-05-31 | 0 |
Output
| domain | seat_days |
|---|---|
| zentrix.io | 700 |
| tiffinly.co | 225 |
| quillpad.in | 110 |
How to solve Google: Prorated Workspace Seat-Days for June 2025
The table stores only the moments a seat count changed, so the first step rebuilds periods: LEAD(effective_date) per customer gives the date the next change takes over, and the current count holds on every day before it. The last change has no successor and runs on indefinitely, which COALESCE(next_date, '2025-07-01') caps at the end of the month.
Each period is then clipped to June as a half-open range [a, b): a = GREATEST(from_date, '2025-06-01'), b = LEAST(next_date, '2025-07-01'). If b > a the period overlaps June for exactly DATEDIFF(b, a) days and contributes seats × DATEDIFF(b, a); otherwise it lies wholly outside June and is skipped. Half-open ranges make the boundary days count once: a change on 2025-06-11 ends the old period on the 10th and starts the new one on the 11th. Days before a customer's first change hold no seats because no period covers them, and a cancellation (0 seats) contributes nothing.
The first alternative is the brute-force check: build the 30 days of June with a recursive CTE and, for each customer and day, look up the latest change on or before that day. The second replaces LEAD with a correlated MIN of later dates and the GREATEST/LEAST with CASE. The window version touches each change once.
Reference solution (MySQL)
WITH periods AS (
SELECT customer_id, seats, effective_date AS from_date,
LEAD(effective_date) OVER (PARTITION BY customer_id ORDER BY effective_date) AS next_date
FROM SeatChange
), clipped AS (
SELECT customer_id, seats,
GREATEST(from_date, '2025-06-01') AS a,
LEAST(COALESCE(next_date, '2025-07-01'), '2025-07-01') AS b
FROM periods
)
SELECT c.domain, SUM(k.seats * DATEDIFF(k.b, k.a)) AS seat_days
FROM clipped k
JOIN WorkspaceCustomer c ON c.customer_id = k.customer_id
WHERE k.b > k.a
GROUP BY c.customer_id, c.domain
HAVING SUM(k.seats * DATEDIFF(k.b, k.a)) > 0
ORDER BY seat_days DESC, c.domainAnother way
WITH RECURSIVE june (d) AS (SELECT CAST('2025-06-01' AS DATE) UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM june WHERE d < '2025-06-30') SELECT c.domain, SUM(s.seats) AS seat_days FROM june j JOIN WorkspaceCustomer c JOIN SeatChange s ON s.customer_id = c.customer_id AND s.effective_date = (SELECT MAX(x.effective_date) FROM SeatChange x WHERE x.customer_id = c.customer_id AND x.effective_date <= j.d) GROUP BY c.customer_id, c.domain HAVING SUM(s.seats) > 0 ORDER BY seat_days DESC, c.domainAnother way
SELECT c.domain, SUM(s.seats * DATEDIFF(CASE WHEN COALESCE((SELECT MIN(n.effective_date) FROM SeatChange n WHERE n.customer_id = s.customer_id AND n.effective_date > s.effective_date), '2025-07-01') < '2025-07-01' THEN (SELECT MIN(n.effective_date) FROM SeatChange n WHERE n.customer_id = s.customer_id AND n.effective_date > s.effective_date) ELSE '2025-07-01' END, CASE WHEN s.effective_date > '2025-06-01' THEN s.effective_date ELSE '2025-06-01' END)) AS seat_days FROM SeatChange s JOIN WorkspaceCustomer c ON c.customer_id = s.customer_id WHERE s.effective_date < '2025-07-01' AND COALESCE((SELECT MIN(n.effective_date) FROM SeatChange n WHERE n.customer_id = s.customer_id AND n.effective_date > s.effective_date), '2025-07-01') > '2025-06-01' GROUP BY c.customer_id, c.domain HAVING SUM(s.seats * DATEDIFF(CASE WHEN COALESCE((SELECT MIN(n.effective_date) FROM SeatChange n WHERE n.customer_id = s.customer_id AND n.effective_date > s.effective_date), '2025-07-01') < '2025-07-01' THEN (SELECT MIN(n.effective_date) FROM SeatChange n WHERE n.customer_id = s.customer_id AND n.effective_date > s.effective_date) ELSE '2025-07-01' END, CASE WHEN s.effective_date > '2025-06-01' THEN s.effective_date ELSE '2025-06-01' END)) > 0 ORDER BY seat_days DESC, c.domain← Google: Cloud VM Cost With Machine Types Filled Forward · Google: Longest YouTube Autoplay Chain in Each Session →