Google: Prorated Workspace Seat-Days for June 2025

Hard
694.

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

ColumnType
customer_idint
domainvarchar

Primary key: customer_id.

One Workspace customer; domain is unique.

Table: SeatChange

ColumnType
customer_idint
effective_datedate
seatsint

Primary key (together): customer_id, effective_date.

The seat count a customer holds from effective_date on, until its next change.

Example

The rows must come back in the order shown.

WorkspaceCustomer

customer_iddomain
1zentrix.io
2quillpad.in
3tiffinly.co
4ledgerly.com
5orbitpay.in

SeatChange

customer_ideffective_dateseats
12025-04-1020
12025-06-1125
22025-06-2010
32025-05-0115
32025-06-160
42025-07-0150
52025-03-015
52025-05-310

Output

domainseat_days
zentrix.io700
tiffinly.co225
quillpad.in110
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.

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

ColumnType
customer_idint
domainvarchar

Primary key: customer_id.

One Workspace customer; domain is unique.

Table: SeatChange

ColumnType
customer_idint
effective_datedate
seatsint

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_iddomain
1zentrix.io
2quillpad.in
3tiffinly.co
4ledgerly.com
5orbitpay.in

SeatChange

customer_ideffective_dateseats
12025-04-1020
12025-06-1125
22025-06-2010
32025-05-0115
32025-06-160
42025-07-0150
52025-03-015
52025-05-310

Output

domainseat_days
zentrix.io700
tiffinly.co225
quillpad.in110

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.domain

Another 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.domain

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