Apple: Developer Proceeds After Commission in March 2025

Medium
608.

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

ColumnType
developer_idint
team_namevarchar
small_businessbool

Primary key: developer_id.

One row per developer team; small_business is 1 for members of the reduced-commission programme.

Table: StoreSale

ColumnTypeValues
sale_idint
developer_idint
sold_ondate
price_centsint
kindenumpaid_app, in_app, subscription
subscriber_yearint

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.

Example

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

DevTeam

developer_idteam_namesmall_business
1Lotus Labs1
2Banyan Apps0
3Indigo Studio0

StoreSale

sale_iddeveloper_idsold_onprice_centskindsubscriber_year
112025-03-01499paid_appNULL
212025-03-15199in_appNULL
322025-03-10999subscription1
422025-03-31999subscription2
522025-04-01999paid_appNULL
632025-02-281999paid_appNULL
722025-03-20299in_appNULL

Output

team_namesalesproceeds_cents
Lotus Labs2593
Banyan Apps31757
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: 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

ColumnType
developer_idint
team_namevarchar
small_businessbool

Primary key: developer_id.

One row per developer team; small_business is 1 for members of the reduced-commission programme.

Table: StoreSale

ColumnType
sale_idint
developer_idint
sold_ondate
price_centsint
kindenum(paid_app, in_app, subscription)
subscriber_yearint

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_idteam_namesmall_business
1Lotus Labs1
2Banyan Apps0
3Indigo Studio0

StoreSale

sale_iddeveloper_idsold_onprice_centskindsubscriber_year
112025-03-01499paid_appNULL
212025-03-15199in_appNULL
322025-03-10999subscription1
422025-03-31999subscription2
522025-04-01999paid_appNULL
632025-02-281999paid_appNULL
722025-03-20299in_appNULL

Output

team_namesalesproceeds_cents
Lotus Labs2593
Banyan Apps31757

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_name

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