Meta: Running Quest Store Revenue per Developer

Medium
764.

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.

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

ColumnType
app_idint
developer_idint
titlevarchar

Primary key: app_id.

One row per app on the store; a developer can publish several.

Table: StorePurchase

ColumnType
purchase_idint
app_idint
purchased_ondate
priceint
refundedbool

Primary key: purchase_id.

One row per purchase, in rupees; refunded is 1 if the buyer got their money back.

Example

The rows must come back in the order shown.

QuestTitle

app_iddeveloper_idtitle
1201Orbit Golf
2201Rocket Kart
3202Zen Garden
4203Deep Sea Diver

StorePurchase

purchase_idapp_idpurchased_onpricerefunded
112025-02-015000
222025-02-013000
312025-02-035001
412025-02-045000
532025-02-022500
632025-02-022500
732025-02-054001
842025-02-037001

Output

developer_idsale_dateday_revenuerunning_revenue
2012025-02-01800800
2012025-02-045001300
2022025-02-02500500
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.

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

ColumnType
app_idint
developer_idint
titlevarchar

Primary key: app_id.

One row per app on the store; a developer can publish several.

Table: StorePurchase

ColumnType
purchase_idint
app_idint
purchased_ondate
priceint
refundedbool

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_iddeveloper_idtitle
1201Orbit Golf
2201Rocket Kart
3202Zen Garden
4203Deep Sea Diver

StorePurchase

purchase_idapp_idpurchased_onpricerefunded
112025-02-015000
222025-02-013000
312025-02-035001
412025-02-045000
532025-02-022500
632025-02-022500
732025-02-054001
842025-02-037001

Output

developer_idsale_dateday_revenuerunning_revenue
2012025-02-01800800
2012025-02-045001300
2022025-02-02500500

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_on

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

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