Meta: Each Reels Creator's Percentile Rank Within Their Country
The Reels creator bonus programme places every creator relative to the others in their own country.
- Difficulty: Hard
- Topics: Window Functions, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #769
Problem statement
The Reels creator bonus programme places every creator relative to the others in their own country. Take each creator's total plays in August 2025 (2025-08-01 to 2025-08-31; a creator with no August plays has 0). Their percentile rank in the country is (r − 1) / (n − 1), where r is their rank by August plays in ascending order (tied creators share the lowest rank of the tie) and n is the number of creators in that country; a country with a single creator gives that creator 0.
Return every creator with creator_id, country, august_plays, pct_rank (the percentile rank rounded to 4 decimal places) and tier: top when the unrounded percentile rank is at least 0.9, rising when it is at least 0.5, otherwise base. 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: Creator
| Column | Type |
|---|---|
| creator_id | int |
| handle | varchar |
| country | char |
Primary key: creator_id.
One row per creator enrolled in the programme; country is a two-letter code.
Table: ReelDailyPlays
| Column | Type |
|---|---|
| creator_id | int |
| play_date | date |
| plays | int |
Primary key: creator_id, play_date.
Plays of all of a creator's reels on one day. Days without plays have no row.
Examples
Example 1
Creator
| creator_id | handle | country |
|---|---|---|
| 1 | chai.coder | IN |
| 2 | run.priya | IN |
| 3 | metro.diaries | IN |
| 4 | goa.sunsets | IN |
| 5 | biryani.hunter | IN |
| 6 | nyc.eats | US |
| 7 | la.skate | US |
| 8 | dubai.drives | AE |
ReelDailyPlays
| creator_id | play_date | plays |
|---|---|---|
| 1 | 2025-08-01 | 200 |
| 1 | 2025-08-15 | 300 |
| 2 | 2025-08-03 | 300 |
| 3 | 2025-08-31 | 300 |
| 4 | 2025-07-31 | 800 |
| 5 | 2025-08-10 | 900 |
| 6 | 2025-08-02 | 100 |
| 7 | 2025-08-20 | 200 |
| 8 | 2025-08-05 | 50 |
| 3 | 2025-09-01 | 999 |
Output
| creator_id | country | august_plays | pct_rank | tier |
|---|---|---|---|---|
| 8 | AE | 50 | 0 | base |
| 4 | IN | 0 | 0 | base |
| 2 | IN | 300 | 0.25 | base |
| 3 | IN | 300 | 0.25 | base |
| 1 | IN | 500 | 0.75 | rising |
| 5 | IN | 900 | 1 | top |
| 6 | US | 100 | 0 | base |
| 7 | US | 200 | 1 | top |
How to solve Meta: Each Reels Creator's Percentile Rank Within Their Country
The ranking must happen over a complete set of creators, so the first step builds one row per creator with their August total. The date range belongs in the ON clause of the LEFT JOIN: in WHERE it would delete the creators whose only plays fall in July or September, and they must instead be ranked with 0 plays. COALESCE(SUM(...), 0) turns their NULL sum into 0.
PERCENT_RANK() is the statement's formula: within each country it orders creators by plays, gives tied creators the same RANK() (the lowest of the tie), and returns (rank − 1) / (n − 1), defined as 0 when the country has a single creator. So the least-played creator is always 0 and a sole leader is 1. The tier is a CASE on the unrounded value — a creator exactly at 0.9, such as the tenth of eleven, is top — and the reported value is rounded to four places for display.
The alternative computes the same number by counting: r − 1 is how many creators in the same country have strictly fewer plays, divided by n − 1. Its tier thresholds are cross-multiplied (10 · below ≥ 9 · (n − 1)) so no division is compared, with n > 1 guarding the one-creator country. It is quadratic per country where the window is one sort; CUME_DIST() would be the related measure that counts ties as at-or-below instead.
Reference solution (MySQL)
WITH totals AS (
SELECT c.creator_id, c.country, COALESCE(SUM(p.plays), 0) AS august_plays
FROM Creator c
LEFT JOIN ReelDailyPlays p ON p.creator_id = c.creator_id
AND p.play_date BETWEEN '2025-08-01' AND '2025-08-31'
GROUP BY c.creator_id, c.country
), ranked AS (
SELECT creator_id, country, august_plays,
PERCENT_RANK() OVER (PARTITION BY country ORDER BY august_plays) AS pr
FROM totals
)
SELECT creator_id, country, august_plays, ROUND(pr, 4) AS pct_rank,
CASE WHEN pr >= 0.9 THEN 'top' WHEN pr >= 0.5 THEN 'rising' ELSE 'base' END AS tier
FROM rankedAnother way
WITH totals AS (
SELECT c.creator_id, c.country,
COALESCE((SELECT SUM(p.plays) FROM ReelDailyPlays p
WHERE p.creator_id = c.creator_id AND p.play_date BETWEEN '2025-08-01' AND '2025-08-31'), 0) AS august_plays
FROM Creator c
), counted AS (
SELECT t.creator_id, t.country, t.august_plays,
(SELECT COUNT(*) FROM totals u WHERE u.country = t.country AND u.august_plays < t.august_plays) AS below,
(SELECT COUNT(*) FROM totals u WHERE u.country = t.country) AS n
FROM totals t
)
SELECT creator_id, country, august_plays,
CASE WHEN n = 1 THEN 0 ELSE ROUND(below / (n - 1), 4) END AS pct_rank,
CASE WHEN n > 1 AND 10 * below >= 9 * (n - 1) THEN 'top'
WHEN n > 1 AND 2 * below >= n - 1 THEN 'rising'
ELSE 'base' END AS tier
FROM counted← Meta: Trending Reels Tags Split Out of Comma-Separated Lists · Meta: The Day Each Campaign Exhausted Its Lifetime Budget →