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
| Column | Type |
|---|---|
| app_id | int |
| app_name | varchar |
| category | varchar |
Primary key: app_id.
One row per app.
Table: AppPurchase
| Column | Type |
|---|---|
| purchase_id | int |
| app_id | int |
| purchased_on | date |
| price_cents | int |
Primary key: purchase_id.
One row per paid download or in-app purchase.
Table: Refund
| Column | Type |
|---|---|
| purchase_id | int |
| refunded_on | date |
Primary key: purchase_id.
A purchase that was refunded, whenever the refund happened.
Examples
Example 1
StoreApp
| app_id | app_name | category |
|---|---|---|
| 1 | Pocket Tabla | Music |
| 2 | Raga Tuner | Music |
| 3 | Kirana Ledger | Productivity |
| 4 | Tiffin Planner | Productivity |
| 5 | Pixel Rangoli | Games |
AppPurchase
| purchase_id | app_id | purchased_on | price_cents |
|---|---|---|---|
| 1 | 1 | 2024-10-05 | 499 |
| 2 | 1 | 2024-11-11 | 499 |
| 3 | 2 | 2024-12-31 | 999 |
| 4 | 2 | 2025-01-01 | 999 |
| 5 | 3 | 2024-10-20 | 299 |
| 6 | 4 | 2024-11-02 | 299 |
| 7 | 4 | 2024-12-09 | 199 |
| 8 | 5 | 2024-09-30 | 999 |
Refund
| purchase_id | refunded_on |
|---|---|
| 7 | 2024-12-12 |
Output
| category | app_name | net_revenue_cents |
|---|---|---|
| Music | Raga Tuner | 999 |
| Productivity | Kirana Ledger | 299 |
| Productivity | Tiffin Planner | 299 |
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 = 1Another 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 →