In-Game Store Revenue by Item Category

Easy
175.

In-Game Store Revenue by Item Category

The monetisation team reviews which kinds of store items earn the money. Only purchases with status completed count; refunded purchases are returned money and are ignored.

For each item category with at least one completed purchase, return category, purchases (the number of completed purchases) and revenue_inr (the sum of their amount_inr). Categories with no completed purchase are left out. Order by revenue_inr descending, then category ascending.

Tables

Table: StoreItem

ColumnTypeValues
item_idint
item_namevarchar
categoryenumSkin, Battle Pass, Currency Pack, Emote, Loot Box

Primary key: item_id.

One row per item sold in the in-game store.

Table: Purchase

ColumnTypeValues
purchase_idint
player_idint
item_idint
amount_inrint
statusenumcompleted, refunded

Primary key: purchase_id.

One row per purchase attempt that was charged. amount_inr is the price paid, in rupees, after any discount.

Example

The rows must come back in the order shown.

StoreItem

item_iditem_namecategory
1Neon Tiger SkinSkin
2Season 12 PassBattle Pass
3600 GemsCurrency Pack
4Bhangra EmoteEmote
5Mystic CrateLoot Box

Purchase

purchase_idplayer_iditem_idamount_inrstatus
1111799completed
2122449completed
3132449completed
4113499completed
5143399completed
6154149refunded
7125199completed

Output

categorypurchasesrevenue_inr
Battle Pass2898
Currency Pack2898
Skin1799
Loot Box1199
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.

In-Game Store Revenue by Item Category — SQL Joins Problem

The monetisation team reviews which kinds of store items earn the money.

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #175

Problem statement

The monetisation team reviews which kinds of store items earn the money. Only purchases with status completed count; refunded purchases are returned money and are ignored.

For each item category with at least one completed purchase, return category, purchases (the number of completed purchases) and revenue_inr (the sum of their amount_inr). Categories with no completed purchase are left out. Order by revenue_inr descending, then category ascending.

Tables

Table: StoreItem

ColumnType
item_idint
item_namevarchar
categoryenum(Skin, Battle Pass, Currency Pack, Emote, Loot Box)

Primary key: item_id.

One row per item sold in the in-game store.

Table: Purchase

ColumnType
purchase_idint
player_idint
item_idint
amount_inrint
statusenum(completed, refunded)

Primary key: purchase_id.

One row per purchase attempt that was charged. amount_inr is the price paid, in rupees, after any discount.

Examples

Example 1

StoreItem

item_iditem_namecategory
1Neon Tiger SkinSkin
2Season 12 PassBattle Pass
3600 GemsCurrency Pack
4Bhangra EmoteEmote
5Mystic CrateLoot Box

Purchase

purchase_idplayer_iditem_idamount_inrstatus
1111799completed
2122449completed
3132449completed
4113499completed
5143399completed
6154149refunded
7125199completed

Output

categorypurchasesrevenue_inr
Battle Pass2898
Currency Pack2898
Skin1799
Loot Box1199

How to solve In-Game Store Revenue by Item Category

Join each purchase to its item to learn the category, filter to status = 'completed', then group by category and take COUNT(*) and SUM(amount_inr). Because the filter runs before grouping, a category whose only purchases were refunded has no rows left and is absent from the result — exactly what the statement asks for — and a category nobody bought from never appears in the inner join at all.

Note that the price comes from the purchase, not from a list price: discounts are already applied in amount_inr, so the sum is the real revenue.

An alternative keeps every purchase and counts conditionally with SUM(CASE WHEN status = 'completed' …), then removes categories with a zero count in an outer filter (or a HAVING). It reads every row once either way. The ORDER BY uses the category name as the tie-break so that two categories with equal revenue come out in a fixed order.

Reference solution (MySQL)

SELECT i.category, COUNT(*) AS purchases, SUM(p.amount_inr) AS revenue_inr
FROM Purchase p
JOIN StoreItem i ON i.item_id = p.item_id
WHERE p.status = 'completed'
GROUP BY i.category
ORDER BY revenue_inr DESC, i.category

Another way

SELECT category, purchases, revenue_inr FROM (
  SELECT i.category,
         SUM(CASE WHEN p.status = 'completed' THEN 1 ELSE 0 END) AS purchases,
         SUM(CASE WHEN p.status = 'completed' THEN p.amount_inr ELSE 0 END) AS revenue_inr
  FROM StoreItem i JOIN Purchase p ON p.item_id = i.item_id
  GROUP BY i.category
) t WHERE purchases > 0
ORDER BY revenue_inr DESC, category

← Players Who Never Queued for Ranked · Gamertags Breaking the Naming Policy →