Apple: Category Leaders by Q4 Net App Revenue

Medium
607.

Apple: Category Leaders by Q4 Net App Revenue

For the App Store's year-end story, the business team wants the top-earning app in each category for Q4 2024 (purchases from 2024-10-01 to 2024-12-31 inclusive), counting only purchases that were not refunded.

Return category, app_name and net_revenue_cents (the sum of price_cents of its counted purchases) for the app with the highest net revenue in each category. If several apps tie for the top, return all of them. Apps with no counted purchase are not considered. 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: StoreApp

ColumnType
app_idint
app_namevarchar
categoryvarchar

Primary key: app_id.

One row per app.

Table: AppPurchase

ColumnType
purchase_idint
app_idint
purchased_ondate
price_centsint

Primary key: purchase_id.

One row per paid download or in-app purchase.

Table: Refund

ColumnType
purchase_idint
refunded_ondate

Primary key: purchase_id.

A purchase that was refunded, whenever the refund happened.

Example

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

StoreApp

app_idapp_namecategory
1Pocket TablaMusic
2Raga TunerMusic
3Kirana LedgerProductivity
4Tiffin PlannerProductivity
5Pixel RangoliGames

AppPurchase

purchase_idapp_idpurchased_onprice_cents
112024-10-05499
212024-11-11499
322024-12-31999
422025-01-01999
532024-10-20299
642024-11-02299
742024-12-09199
852024-09-30999

Refund

purchase_idrefunded_on
72024-12-12

Output

categoryapp_namenet_revenue_cents
MusicRaga Tuner999
ProductivityKirana Ledger299
ProductivityTiffin Planner299
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: Category Leaders by Q4 Net App Revenue — SQL Problem

For the App Store's year-end story, the business team wants the top-earning app in each category for Q4 2024 (purchases from 2024-10-01 to 2024-12-31…

  • Difficulty: Medium
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #607

Problem statement

For the App Store's year-end story, the business team wants the top-earning app in each category for Q4 2024 (purchases from 2024-10-01 to 2024-12-31 inclusive), counting only purchases that were not refunded.

Return category, app_name and net_revenue_cents (the sum of price_cents of its counted purchases) for the app with the highest net revenue in each category. If several apps tie for the top, return all of them. Apps with no counted purchase are not considered. 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: StoreApp

ColumnType
app_idint
app_namevarchar
categoryvarchar

Primary key: app_id.

One row per app.

Table: AppPurchase

ColumnType
purchase_idint
app_idint
purchased_ondate
price_centsint

Primary key: purchase_id.

One row per paid download or in-app purchase.

Table: Refund

ColumnType
purchase_idint
refunded_ondate

Primary key: purchase_id.

A purchase that was refunded, whenever the refund happened.

Examples

Example 1

StoreApp

app_idapp_namecategory
1Pocket TablaMusic
2Raga TunerMusic
3Kirana LedgerProductivity
4Tiffin PlannerProductivity
5Pixel RangoliGames

AppPurchase

purchase_idapp_idpurchased_onprice_cents
112024-10-05499
212024-11-11499
322024-12-31999
422025-01-01999
532024-10-20299
642024-11-02299
742024-12-09199
852024-09-30999

Refund

purchase_idrefunded_on
72024-12-12

Output

categoryapp_namenet_revenue_cents
MusicRaga Tuner999
ProductivityKirana Ledger299
ProductivityTiffin Planner299

How to solve Apple: Category Leaders by Q4 Net App Revenue

Split the question in two. Step one is a per-app total: keep purchases dated inside Q4 (both 2024-10-01 and 2024-12-31 count) that have no row in Refund — an anti join, written with NOT EXISTS or a LEFT JOIN that keeps the unmatched rows — and sum their prices per app. A refund issued in January still removes a December purchase, because the statement counts purchases that were refunded at any time.

Step two picks the leader of each category. RANK() OVER (PARTITION BY category ORDER BY net_revenue_cents DESC) numbers apps inside their category; equal revenue gets equal rank, so filtering = 1 returns every tied leader, as asked. ROW_NUMBER() would silently pick one of them.

The alternative compares each app's total with the category maximum via a correlated subquery on the same CTE. Both group the purchases once; the window version then sorts each category's apps, the subquery version scans them per app — fine at this size.

Reference solution (MySQL)

WITH net AS (
  SELECT a.category, a.app_name, SUM(p.price_cents) AS net_revenue_cents
  FROM AppPurchase p
  JOIN StoreApp a ON a.app_id = p.app_id
  WHERE p.purchased_on BETWEEN '2024-10-01' AND '2024-12-31'
    AND NOT EXISTS (SELECT 1 FROM Refund r WHERE r.purchase_id = p.purchase_id)
  GROUP BY a.app_id, a.category, a.app_name
), ranked AS (
  SELECT category, app_name, net_revenue_cents,
         RANK() OVER (PARTITION BY category ORDER BY net_revenue_cents DESC) AS pos
  FROM net
)
SELECT category, app_name, net_revenue_cents FROM ranked WHERE pos = 1

Another way

WITH net AS (
  SELECT a.category, a.app_name, SUM(p.price_cents) AS net_revenue_cents
  FROM AppPurchase p JOIN StoreApp a ON a.app_id = p.app_id
  LEFT JOIN Refund r ON r.purchase_id = p.purchase_id
  WHERE r.purchase_id IS NULL AND p.purchased_on >= '2024-10-01' AND p.purchased_on < '2025-01-01'
  GROUP BY a.app_id, a.category, a.app_name
)
SELECT n.category, n.app_name, n.net_revenue_cents FROM net n
WHERE n.net_revenue_cents = (SELECT MAX(m.net_revenue_cents) FROM net m WHERE m.category = n.category)

← Apple: Daily Share of Checked-In Devices on iOS 18 · Apple: Developer Proceeds After Commission in March 2025 →