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
| Column | Type |
|---|---|
| item_id | int |
| item_name | varchar |
| category | enum(Skin, Battle Pass, Currency Pack, Emote, Loot Box) |
Primary key: item_id.
One row per item sold in the in-game store.
Table: Purchase
| Column | Type |
|---|---|
| purchase_id | int |
| player_id | int |
| item_id | int |
| amount_inr | int |
| status | enum(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_id | item_name | category |
|---|---|---|
| 1 | Neon Tiger Skin | Skin |
| 2 | Season 12 Pass | Battle Pass |
| 3 | 600 Gems | Currency Pack |
| 4 | Bhangra Emote | Emote |
| 5 | Mystic Crate | Loot Box |
Purchase
| purchase_id | player_id | item_id | amount_inr | status |
|---|---|---|---|---|
| 1 | 11 | 1 | 799 | completed |
| 2 | 12 | 2 | 449 | completed |
| 3 | 13 | 2 | 449 | completed |
| 4 | 11 | 3 | 499 | completed |
| 5 | 14 | 3 | 399 | completed |
| 6 | 15 | 4 | 149 | refunded |
| 7 | 12 | 5 | 199 | completed |
Output
| category | purchases | revenue_inr |
|---|---|---|
| Battle Pass | 2 | 898 |
| Currency Pack | 2 | 898 |
| Skin | 1 | 799 |
| Loot Box | 1 | 199 |
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.categoryAnother 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 →