Meta: Quest Store Apps Rated by at Least Three Players
The Quest Store hides an app's rating summary until enough different people have rated it.
- Difficulty: Easy
- Topics: Aggregation, Joins
- Dialect: MySQL
- Problem: #755
Problem statement
The Quest Store hides an app's rating summary until enough different people have rated it. A player may rate an app again after an update, and every rating is kept as its own row.
Return every app rated by at least three different players, with the columns app_name, players (the number of distinct players who rated it) and ratings (the number of rating rows, re-ratings included). Return the rows in any order.
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: QuestApp
| Column | Type |
|---|---|
| app_id | int |
| app_name | varchar |
| genre | varchar |
Primary key: app_id.
One row per app in the store; names are unique.
Table: AppRating
| Column | Type |
|---|---|
| rating_id | int |
| app_id | int |
| player_id | int |
| stars | int |
| rated_at | date |
Primary key: rating_id.
One row per rating submitted (1–5 stars). The same player can appear more than once for an app.
Examples
Example 1
QuestApp
| app_id | app_name | genre |
|---|---|---|
| 1 | Orbit Golf | Sports |
| 2 | Haunted Haveli | Horror |
| 3 | Cricket Nets VR | Sports |
| 4 | Zen Garden | Relaxation |
AppRating
| rating_id | app_id | player_id | stars | rated_at |
|---|---|---|---|---|
| 1 | 1 | 21 | 4 | 2025-03-01 |
| 2 | 1 | 22 | 5 | 2025-03-02 |
| 3 | 1 | 21 | 5 | 2025-03-20 |
| 4 | 1 | 23 | 3 | 2025-03-21 |
| 5 | 2 | 21 | 2 | 2025-03-05 |
| 6 | 2 | 22 | 4 | 2025-03-06 |
| 7 | 2 | 22 | 5 | 2025-03-15 |
| 8 | 3 | 24 | 5 | 2025-03-07 |
| 9 | 3 | 25 | 4 | 2025-03-07 |
| 10 | 3 | 26 | 4 | 2025-03-08 |
| 11 | 3 | 27 | 1 | 2025-03-09 |
Output
| app_name | players | ratings |
|---|---|---|
| Orbit Golf | 3 | 4 |
| Cricket Nets VR | 4 | 4 |
How to solve Meta: Quest Store Apps Rated by at Least Three Players
The threshold is about people, not rows: a player who rated an app twice must count once. COUNT(DISTINCT r.player_id) does exactly that within each app's group, while a plain COUNT(*) in the same SELECT counts every rating row, re-ratings included — the two columns the statement asks for.
The rule then goes in HAVING, because it is a condition on an aggregate: HAVING COUNT(DISTINCT r.player_id) >= 3. Apps with no ratings never join and so never appear, which is right since zero is below three. In the example, Orbit Golf has four ratings from three players and qualifies, Haunted Haveli has three ratings from only two players and does not.
The alternative makes the distinct count explicit: it first reduces the ratings to distinct (app, player) pairs and counts those, counts the raw rows in a second derived table, and joins both to the apps. It is longer but shows what COUNT(DISTINCT …) does. Both are a scan plus a sort or hash per app.
Reference solution (MySQL)
SELECT q.app_name, COUNT(DISTINCT r.player_id) AS players, COUNT(*) AS ratings
FROM QuestApp q
JOIN AppRating r ON r.app_id = q.app_id
GROUP BY q.app_id, q.app_name
HAVING COUNT(DISTINCT r.player_id) >= 3Another way
SELECT q.app_name, p.players, c.ratings
FROM QuestApp q
JOIN (SELECT app_id, COUNT(*) AS players FROM (SELECT DISTINCT app_id, player_id FROM AppRating) d GROUP BY app_id) p ON p.app_id = q.app_id
JOIN (SELECT app_id, COUNT(*) AS ratings FROM AppRating GROUP BY app_id) c ON c.app_id = q.app_id
WHERE p.players >= 3← Meta: Moderation Reports Reviewed Past Their SLA · Meta: Ad Set Metrics Unpivoted Into Long Format →