Meta: Running Quest Store Revenue per Developer
Developers on the Meta Quest Store get a revenue chart that shows each sales day and the total earned so far. Refunded purchases earn nothing.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #764
Problem statement
Developers on the Meta Quest Store get a revenue chart that shows each sales day and the total earned so far. Refunded purchases earn nothing.
For each developer and each date on which at least one of their apps had a non-refunded sale, return developer_id, sale_date, day_revenue (that day's non-refunded revenue across all their apps) and running_revenue (their non-refunded revenue from their first sale up to and including that date). Order the rows by developer_id, then sale_date.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: QuestTitle
| Column | Type |
|---|---|
| app_id | int |
| developer_id | int |
| title | varchar |
Primary key: app_id.
One row per app on the store; a developer can publish several.
Table: StorePurchase
| Column | Type |
|---|---|
| purchase_id | int |
| app_id | int |
| purchased_on | date |
| price | int |
| refunded | bool |
Primary key: purchase_id.
One row per purchase, in rupees; refunded is 1 if the buyer got their money back.
Examples
Example 1
QuestTitle
| app_id | developer_id | title |
|---|---|---|
| 1 | 201 | Orbit Golf |
| 2 | 201 | Rocket Kart |
| 3 | 202 | Zen Garden |
| 4 | 203 | Deep Sea Diver |
StorePurchase
| purchase_id | app_id | purchased_on | price | refunded |
|---|---|---|---|---|
| 1 | 1 | 2025-02-01 | 500 | 0 |
| 2 | 2 | 2025-02-01 | 300 | 0 |
| 3 | 1 | 2025-02-03 | 500 | 1 |
| 4 | 1 | 2025-02-04 | 500 | 0 |
| 5 | 3 | 2025-02-02 | 250 | 0 |
| 6 | 3 | 2025-02-02 | 250 | 0 |
| 7 | 3 | 2025-02-05 | 400 | 1 |
| 8 | 4 | 2025-02-03 | 700 | 1 |
Output
| developer_id | sale_date | day_revenue | running_revenue |
|---|---|---|---|
| 201 | 2025-02-01 | 800 | 800 |
| 201 | 2025-02-04 | 500 | 1300 |
| 202 | 2025-02-02 | 500 | 500 |
How to solve Meta: Running Quest Store Revenue per Developer
First reduce the purchases to one row per developer per day: join each purchase to its app to learn the developer, drop refunds in WHERE, and GROUP BY developer_id, purchased_on. Several apps or several purchases on one day collapse into a single day_revenue, and a developer whose every sale was refunded has no rows at all.
The running total is a window over those grouped rows. Window functions are evaluated after GROUP BY, so SUM(SUM(p.price)) OVER (PARTITION BY developer_id ORDER BY purchased_on) adds up the daily sums in date order within each developer; because every row is a distinct date, the default frame (everything up to the current row) is exactly "up to and including this date". Doing the window on the raw purchases instead would repeat a day once per purchase.
The alternatives compute the daily table in a CTE and sum it either with a correlated subquery or a self-join on b.sale_date <= a.sale_date — quadratic per developer, but they show what the window does in one sorted pass.
Reference solution (MySQL)
SELECT q.developer_id, p.purchased_on AS sale_date, SUM(p.price) AS day_revenue,
SUM(SUM(p.price)) OVER (PARTITION BY q.developer_id ORDER BY p.purchased_on) AS running_revenue
FROM StorePurchase p
JOIN QuestTitle q ON q.app_id = p.app_id
WHERE p.refunded = 0
GROUP BY q.developer_id, p.purchased_on
ORDER BY q.developer_id, p.purchased_onAnother way
WITH daily AS (
SELECT q.developer_id, p.purchased_on AS sale_date, SUM(p.price) AS day_revenue
FROM StorePurchase p JOIN QuestTitle q ON q.app_id = p.app_id
WHERE p.refunded = 0 GROUP BY q.developer_id, p.purchased_on
)
SELECT d.developer_id, d.sale_date, d.day_revenue,
(SELECT SUM(e.day_revenue) FROM daily e WHERE e.developer_id = d.developer_id AND e.sale_date <= d.sale_date) AS running_revenue
FROM daily d
ORDER BY d.developer_id, d.sale_dateAnother way
WITH daily AS (
SELECT q.developer_id, p.purchased_on AS sale_date, SUM(p.price) AS day_revenue
FROM StorePurchase p JOIN QuestTitle q ON q.app_id = p.app_id
WHERE p.refunded = 0 GROUP BY q.developer_id, p.purchased_on
)
SELECT a.developer_id, a.sale_date, a.day_revenue, SUM(b.day_revenue) AS running_revenue
FROM daily a JOIN daily b ON b.developer_id = a.developer_id AND b.sale_date <= a.sale_date
GROUP BY a.developer_id, a.sale_date, a.day_revenue
ORDER BY a.developer_id, a.sale_date← Meta: Messenger Call Drops by Major App Version · Meta: Marketplace Listings Against the Same Month Last Year →