Meta: Each Reels Creator's Percentile Rank Within Their Country

Hard
769.

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. 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

ColumnType
creator_idint
handlevarchar
countrychar

Primary key: creator_id.

One row per creator enrolled in the programme; country is a two-letter code.

Table: ReelDailyPlays

ColumnType
creator_idint
play_datedate
playsint

Primary key (together): creator_id, play_date.

Plays of all of a creator's reels on one day. Days without plays have no row.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Creator

creator_idhandlecountry
1chai.coderIN
2run.priyaIN
3metro.diariesIN
4goa.sunsetsIN
5biryani.hunterIN
6nyc.eatsUS
7la.skateUS
8dubai.drivesAE

ReelDailyPlays

creator_idplay_dateplays
12025-08-01200
12025-08-15300
22025-08-03300
32025-08-31300
42025-07-31800
52025-08-10900
62025-08-02100
72025-08-20200
82025-08-0550
32025-09-01999

Output

creator_idcountryaugust_playspct_ranktier
8AE500base
4IN00base
2IN3000.25base
3IN3000.25base
1IN5000.75rising
5IN9001top
6US1000base
7US2001top
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: 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

ColumnType
creator_idint
handlevarchar
countrychar

Primary key: creator_id.

One row per creator enrolled in the programme; country is a two-letter code.

Table: ReelDailyPlays

ColumnType
creator_idint
play_datedate
playsint

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_idhandlecountry
1chai.coderIN
2run.priyaIN
3metro.diariesIN
4goa.sunsetsIN
5biryani.hunterIN
6nyc.eatsUS
7la.skateUS
8dubai.drivesAE

ReelDailyPlays

creator_idplay_dateplays
12025-08-01200
12025-08-15300
22025-08-03300
32025-08-31300
42025-07-31800
52025-08-10900
62025-08-02100
72025-08-20200
82025-08-0550
32025-09-01999

Output

creator_idcountryaugust_playspct_ranktier
8AE500base
4IN00base
2IN3000.25base
3IN3000.25base
1IN5000.75rising
5IN9001top
6US1000base
7US2001top

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 ranked

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