Apple: Developer Proceeds After Commission in March 2025
App Store finance closes the month by computing what each developer team is paid after the store's commission.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation, Joins
- Dialect: MySQL
- Problem: #608
Problem statement
App Store finance closes the month by computing what each developer team is paid after the store's commission. In this exercise the commission on a sale is 15% if the team is in the small-business programme (small_business = 1), or if the sale is a subscription in its second year or later (subscriber_year >= 2); every other sale pays 30%.
For every team with at least one sale in March 2025, return team_name, sales (the number of March sales) and proceeds_cents: the sum over its March sales of price_cents × (100 − commission %) / 100, rounded down to a whole cent after summing. 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: DevTeam
| Column | Type |
|---|---|
| developer_id | int |
| team_name | varchar |
| small_business | bool |
Primary key: developer_id.
One row per developer team; small_business is 1 for members of the reduced-commission programme.
Table: StoreSale
| Column | Type |
|---|---|
| sale_id | int |
| developer_id | int |
| sold_on | date |
| price_cents | int |
| kind | enum(paid_app, in_app, subscription) |
| subscriber_year | int |
Primary key: sale_id.
subscriber_year is the subscriber's year of paid service for a subscription (1, 2, …) and NULL for any other kind.
Examples
Example 1
DevTeam
| developer_id | team_name | small_business |
|---|---|---|
| 1 | Lotus Labs | 1 |
| 2 | Banyan Apps | 0 |
| 3 | Indigo Studio | 0 |
StoreSale
| sale_id | developer_id | sold_on | price_cents | kind | subscriber_year |
|---|---|---|---|---|---|
| 1 | 1 | 2025-03-01 | 499 | paid_app | NULL |
| 2 | 1 | 2025-03-15 | 199 | in_app | NULL |
| 3 | 2 | 2025-03-10 | 999 | subscription | 1 |
| 4 | 2 | 2025-03-31 | 999 | subscription | 2 |
| 5 | 2 | 2025-04-01 | 999 | paid_app | NULL |
| 6 | 3 | 2025-02-28 | 1999 | paid_app | NULL |
| 7 | 2 | 2025-03-20 | 299 | in_app | NULL |
Output
| team_name | sales | proceeds_cents |
|---|---|---|
| Lotus Labs | 2 | 593 |
| Banyan Apps | 3 | 1757 |
How to solve Apple: Developer Proceeds After Commission in March 2025
Each sale carries its own commission rate, so the rate is a per-row CASE: 15 when the team is a small business, 15 when the sale is a subscription in year two or later, otherwise 30. Order the branches so the reduced rates are checked first; a sale that is not a subscription has subscriber_year NULL, and NULL >= 2 is unknown rather than true, so it correctly falls to the 30% default.
The proceeds are price × (100 − rate) / 100. Summing the integer numerators first and dividing once at the end keeps everything exact and matches the statement's "rounded down after summing": FLOOR(SUM(…) / 100), or equivalently SUM(…) DIV 100 for these positive amounts. Rounding each sale separately would lose up to a cent per sale.
The March filter is a plain date range (or YEAR and MONTH), applied before grouping; teams without a March sale never form a group. The alternative swaps the CASE for one IF with an OR. Both are a single join and aggregate.
Reference solution (MySQL)
SELECT t.team_name,
COUNT(*) AS sales,
FLOOR(SUM(s.price_cents * (100 - CASE
WHEN t.small_business = 1 THEN 15
WHEN s.kind = 'subscription' AND s.subscriber_year >= 2 THEN 15
ELSE 30 END)) / 100) AS proceeds_cents
FROM StoreSale s
JOIN DevTeam t ON t.developer_id = s.developer_id
WHERE s.sold_on BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY t.developer_id, t.team_nameAnother way
SELECT t.team_name, x.sales, x.kept DIV 100 AS proceeds_cents
FROM DevTeam t
JOIN (SELECT s.developer_id, COUNT(*) AS sales,
SUM(s.price_cents * IF(d.small_business = 1 OR (s.kind = 'subscription' AND s.subscriber_year > 1), 85, 70)) AS kept
FROM StoreSale s JOIN DevTeam d ON d.developer_id = s.developer_id
WHERE YEAR(s.sold_on) = 2025 AND MONTH(s.sold_on) = 3
GROUP BY s.developer_id) x ON x.developer_id = t.developer_id← Apple: Category Leaders by Q4 Net App Revenue · Apple: Genius Bar Repeat Visits Within 14 Days →