Google: Play Service Fee and the Purchase That Crossed the Tier
In this exercise the Play Store keeps 15% of a developer's first 1,000 USD of sales in each calendar year and 30% of everything above that within the same…
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #692
Problem statement
In this exercise the Play Store keeps 15% of a developer's first 1,000 USD of sales in each calendar year and 30% of everything above that within the same year. A purchase that straddles the 1,000 mark is split: the part up to 1,000 is charged 15% and the rest 30%.
Purchases are taken in order of purchased_at, then purchase_id. For every developer and year with sales, return developer_id, sales_year, gross_usd (the year's sales), fee_usd (the year's fee, rounded to 2 decimals) and crossing_purchase_id — the purchase during which the year's running sales first went above 1,000 (reaching exactly 1,000 is not above), or NULL if they never did. Order by developer_id, then sales_year.
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: PlayPurchase
| Column | Type |
|---|---|
| purchase_id | int |
| developer_id | int |
| purchased_at | datetime |
| amount_usd | int |
Primary key: purchase_id.
One paid app or in-app purchase, in whole US dollars (always positive).
Examples
Example 1
PlayPurchase
| purchase_id | developer_id | purchased_at | amount_usd |
|---|---|---|---|
| 1 | 7 | 2024-12-20 10:00:00 | 400 |
| 2 | 7 | 2025-01-05 09:30:00 | 600 |
| 3 | 7 | 2025-02-01 18:00:00 | 300 |
| 4 | 7 | 2025-03-01 12:00:00 | 250 |
| 5 | 9 | 2025-04-01 09:00:00 | 990 |
| 8 | 9 | 2025-05-05 10:00:00 | 50 |
| 6 | 9 | 2025-05-05 10:00:00 | 10 |
Output
| developer_id | sales_year | gross_usd | fee_usd | crossing_purchase_id |
|---|---|---|---|---|
| 7 | 2024 | 400 | 60 | NULL |
| 7 | 2025 | 1150 | 195 | 4 |
| 9 | 2025 | 1050 | 165 | 8 |
How to solve Google: Play Service Fee and the Purchase That Crossed the Tier
Two different questions share one running total. SUM(amount_usd) OVER (PARTITION BY developer_id, YEAR(purchased_at) ORDER BY purchased_at, purchase_id ROWS … CURRENT ROW) gives, for every purchase, the year's sales up to and including it; the purchase_id tiebreak makes the order total when two purchases share a timestamp, so the running totals are deterministic.
The crossing purchase is the one where the total jumps over the mark: running > 1000 and running - amount_usd <= 1000. Exactly one purchase per developer-year can satisfy that (amounts are positive), and none does if the year never passes 1,000 — including a year that stops at exactly 1,000. A LEFT JOIN onto the yearly totals keeps those years with NULL.
The fee splits at the mark, and because every dollar is either below or above it, the year's fee is 0.15 × LEAST(gross, 1000) + 0.30 × GREATEST(gross − 1000, 0) — order does not change it. The alternative proves that by charging each purchase its own split (low = the part still under the mark) and summing, with the total-before computed by a correlated subquery instead of a window. The window version sorts each developer-year once.
Reference solution (MySQL)
WITH p AS (
SELECT purchase_id, developer_id, YEAR(purchased_at) AS sales_year, amount_usd,
SUM(amount_usd) OVER (PARTITION BY developer_id, YEAR(purchased_at)
ORDER BY purchased_at, purchase_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM PlayPurchase
), yr AS (
SELECT developer_id, sales_year, SUM(amount_usd) AS gross
FROM p
GROUP BY developer_id, sales_year
), crossing AS (
SELECT developer_id, sales_year, purchase_id
FROM p
WHERE running > 1000 AND running - amount_usd <= 1000
)
SELECT y.developer_id, y.sales_year, y.gross AS gross_usd,
ROUND(0.15 * LEAST(y.gross, 1000) + 0.30 * GREATEST(y.gross - 1000, 0), 2) AS fee_usd,
c.purchase_id AS crossing_purchase_id
FROM yr y
LEFT JOIN crossing c ON c.developer_id = y.developer_id AND c.sales_year = y.sales_year
ORDER BY y.developer_id, y.sales_yearAnother way
WITH p AS (SELECT a.purchase_id, a.developer_id, YEAR(a.purchased_at) AS sales_year, a.amount_usd, (SELECT COALESCE(SUM(b.amount_usd), 0) FROM PlayPurchase b WHERE b.developer_id = a.developer_id AND YEAR(b.purchased_at) = YEAR(a.purchased_at) AND (b.purchased_at < a.purchased_at OR (b.purchased_at = a.purchased_at AND b.purchase_id < a.purchase_id))) AS before_it FROM PlayPurchase a), s AS (SELECT purchase_id, developer_id, sales_year, amount_usd, before_it, GREATEST(0, LEAST(amount_usd, 1000 - before_it)) AS low FROM p) SELECT developer_id, sales_year, SUM(amount_usd) AS gross_usd, ROUND(SUM(0.15 * low + 0.30 * (amount_usd - low)), 2) AS fee_usd, MAX(CASE WHEN before_it <= 1000 AND before_it + amount_usd > 1000 THEN purchase_id END) AS crossing_purchase_id FROM s GROUP BY developer_id, sales_year ORDER BY developer_id, sales_year← Google: YouTube Channels Eligible for the Partner Programme · Google: Cloud VM Cost With Machine Types Filled Forward →