Apple: In-App Revenue at the Price in Effect on Each Day

Hard
618.

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

ColumnType
product_idint
product_namevarchar
base_price_centsint

Primary key: product_id.

One row per in-app product, with its launch price.

Table: PriceChange

ColumnType
product_idint
effective_ondate
new_price_centsint

Primary key (together): product_id, effective_on.

A product's price from effective_on until its next change.

Table: IapPurchase

ColumnType
purchase_idint
product_idint
purchased_ondate
quantityint

Primary key: purchase_id.

One row per purchase; quantity is at least 1.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

IapProduct

product_idproduct_namebase_price_cents
1Gem Pack S99
2Gem Pack L499
3Pro Unlock999

PriceChange

product_ideffective_onnew_price_cents
12025-02-01129
12025-03-01149
22025-02-15449

IapPurchase

purchase_idproduct_idpurchased_onquantity
112025-01-202
212025-02-011
312025-02-281
412025-03-053
522025-02-141
622025-02-152

Output

product_nameunitsrevenue_cents
Gem Pack S7903
Gem Pack L31397
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.

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

ColumnType
product_idint
product_namevarchar
base_price_centsint

Primary key: product_id.

One row per in-app product, with its launch price.

Table: PriceChange

ColumnType
product_idint
effective_ondate
new_price_centsint

Primary key: product_id, effective_on.

A product's price from effective_on until its next change.

Table: IapPurchase

ColumnType
purchase_idint
product_idint
purchased_ondate
quantityint

Primary key: purchase_id.

One row per purchase; quantity is at least 1.

Examples

Example 1

IapProduct

product_idproduct_namebase_price_cents
1Gem Pack S99
2Gem Pack L499
3Pro Unlock999

PriceChange

product_ideffective_onnew_price_cents
12025-02-01129
12025-03-01149
22025-02-15449

IapPurchase

purchase_idproduct_idpurchased_onquantity
112025-01-202
212025-02-011
312025-02-281
412025-03-053
522025-02-141
622025-02-152

Output

product_nameunitsrevenue_cents
Gem Pack S7903
Gem Pack L31397

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_name

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

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