Apple: In-App Revenue at the Price in Effect on Each Day
Developers can change an in-app product's price, and a change takes effect from its effective_on date.
- Difficulty: Hard
- Topics: Subqueries, Joins, Dates
- Dialect: MySQL
- Problem: #618
Problem statement
Developers can change an in-app product's price, and a change takes effect from its effective_on date. The payments team recomputes revenue to check the ledger.
A purchase is charged the new_price_cents of the latest price change of its product with effective_on on or before the purchase date; if the product had no change by then, it is charged the product's base_price_cents. For every product with at least one purchase, return product_name, units (total quantity) and revenue_cents (the sum of quantity × charged price). Return the rows in any order.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: IapProduct
| Column | Type |
|---|---|
| product_id | int |
| product_name | varchar |
| base_price_cents | int |
Primary key: product_id.
One row per in-app product, with its launch price.
Table: PriceChange
| Column | Type |
|---|---|
| product_id | int |
| effective_on | date |
| new_price_cents | int |
Primary key: product_id, effective_on.
A product's price from effective_on until its next change.
Table: IapPurchase
| Column | Type |
|---|---|
| purchase_id | int |
| product_id | int |
| purchased_on | date |
| quantity | int |
Primary key: purchase_id.
One row per purchase; quantity is at least 1.
Examples
Example 1
IapProduct
| product_id | product_name | base_price_cents |
|---|---|---|
| 1 | Gem Pack S | 99 |
| 2 | Gem Pack L | 499 |
| 3 | Pro Unlock | 999 |
PriceChange
| product_id | effective_on | new_price_cents |
|---|---|---|
| 1 | 2025-02-01 | 129 |
| 1 | 2025-03-01 | 149 |
| 2 | 2025-02-15 | 449 |
IapPurchase
| purchase_id | product_id | purchased_on | quantity |
|---|---|---|---|
| 1 | 1 | 2025-01-20 | 2 |
| 2 | 1 | 2025-02-01 | 1 |
| 3 | 1 | 2025-02-28 | 1 |
| 4 | 1 | 2025-03-05 | 3 |
| 5 | 2 | 2025-02-14 | 1 |
| 6 | 2 | 2025-02-15 | 2 |
Output
| product_name | units | revenue_cents |
|---|---|---|
| Gem Pack S | 7 | 903 |
| Gem Pack L | 3 | 1397 |
How to solve Apple: In-App Revenue at the Price in Effect on Each Day
Each purchase needs the price as of its date: among its product's price changes, the one with the greatest effective_on that is not after the purchase. That is a per-row lookup, and the most direct form is a correlated scalar subquery — filter the product's changes to effective_on <= purchased_on, sort newest first, take one row. The comparison is inclusive, so a purchase on the day a change takes effect pays the new price.
A purchase made before any change finds nothing; the scalar subquery yields NULL and COALESCE(…, base_price_cents) falls back to the launch price. Multiply by quantity and sum per product; products never purchased do not appear because the query starts from purchases.
Two set-based alternatives: LEFT JOIN every eligible change to each purchase and keep the newest with ROW_NUMBER() … = 1 (a purchase with no eligible change keeps its single NULL row), or join on effective_on = MAX(effective_on) of eligible changes. With an index on (product_id, effective_on) the subquery is one index seek per purchase.
Reference solution (MySQL)
SELECT pr.product_name, SUM(p.quantity) AS units,
SUM(p.quantity * COALESCE(
(SELECT c.new_price_cents FROM PriceChange c
WHERE c.product_id = p.product_id AND c.effective_on <= p.purchased_on
ORDER BY c.effective_on DESC LIMIT 1),
pr.base_price_cents)) AS revenue_cents
FROM IapPurchase p
JOIN IapProduct pr ON pr.product_id = p.product_id
GROUP BY pr.product_id, pr.product_nameAnother way
WITH matched AS (
SELECT p.purchase_id, p.product_id, p.quantity, c.new_price_cents,
ROW_NUMBER() OVER (PARTITION BY p.purchase_id ORDER BY c.effective_on DESC) AS rn
FROM IapPurchase p
LEFT JOIN PriceChange c ON c.product_id = p.product_id AND c.effective_on <= p.purchased_on
)
SELECT pr.product_name, SUM(m.quantity) AS units, SUM(m.quantity * COALESCE(m.new_price_cents, pr.base_price_cents)) AS revenue_cents
FROM matched m JOIN IapProduct pr ON pr.product_id = m.product_id
WHERE m.rn = 1
GROUP BY pr.product_id, pr.product_nameAnother way
SELECT pr.product_name, SUM(p.quantity) AS units, SUM(p.quantity * COALESCE(c.new_price_cents, pr.base_price_cents)) AS revenue_cents
FROM IapPurchase p
JOIN IapProduct pr ON pr.product_id = p.product_id
LEFT JOIN PriceChange c ON c.product_id = p.product_id
AND c.effective_on = (SELECT MAX(x.effective_on) FROM PriceChange x WHERE x.product_id = p.product_id AND x.effective_on <= p.purchased_on)
GROUP BY pr.product_id, pr.product_name← Apple: Subscription Billing Lapses Recovered in the Grace Period · Apple: Devices That Moved to an Older iOS Version →