Tournament Sign-up Rate

An e-sports league runs open tournaments that any registered player can enter. Organisers want to know how much of the player base each tournament attracted.

For every tournament that has at least one registration, return tournament_id and signup_pct: the percentage of all players in players who registered for that tournament, rounded to 2 decimal places (for example 33.33). A player who submitted the form more than once for the same tournament counts once.

Order the rows by signup_pct from highest to lowest, then by tournament_id ascending. If there are no registrations, return no rows.

Tables

players — One row per registered player of the league.

registrations — One row per submitted entry form. A player may submit the form for the same tournament more than once.

Example

players

registrations

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Aggregation & Grouping · problem 12 of 70 · SQLite

The idea

Group registrations by tournament_id. Inside each group, COUNT(DISTINCT player_id) gives the number of different players who entered, so a resubmitted form does not inflate the count.

The denominator is the whole player base, which does not depend on the group, so a scalar subquery (SELECT COUNT(*) FROM players) supplies it. Players who never registered for anything still belong to that base, which is why counting players from registrations would be wrong.

SQLite divides integers by truncating, so the expression starts with 100.0 * to force real arithmetic before ROUND(…, 2). Finally, sorting by signup_pct DESC, tournament_id makes the order fully deterministic even when two tournaments share the same rate.

The alternative removes duplicates first with SELECT DISTINCT in a CTE, then counts plain rows and cross joins a one-row total.

Solution

SELECT
  r.tournament_id,
  ROUND(100.0 * COUNT(DISTINCT r.player_id) / (SELECT COUNT(*) FROM players), 2) AS signup_pct
FROM registrations AS r
GROUP BY r.tournament_id
ORDER BY signup_pct DESC, r.tournament_id;

Another way

WITH entrants AS (
  SELECT DISTINCT tournament_id, player_id
  FROM registrations
),
total AS (
  SELECT COUNT(*) AS n FROM players
)
SELECT
  e.tournament_id,
  ROUND(COUNT(*) * 100.0 / t.n, 2) AS signup_pct
FROM entrants AS e
CROSS JOIN total AS t
GROUP BY e.tournament_id, t.n
ORDER BY signup_pct DESC, e.tournament_id;

Why common answers fail

Counts form submissions instead of distinct players, so a player who resubmitted is counted twice.

SELECT tournament_id, ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM players), 2) AS signup_pct
FROM registrations
GROUP BY tournament_id
ORDER BY signup_pct DESC, tournament_id;

Uses integer arithmetic, so 4 out of 6 players becomes 66 instead of 66.67.

SELECT tournament_id, ROUND(100 * COUNT(DISTINCT player_id) / (SELECT COUNT(*) FROM players), 2) AS signup_pct
FROM registrations
GROUP BY tournament_id
ORDER BY signup_pct DESC, tournament_id;

Divides by the players who registered somewhere instead of all players, ignoring those who never signed up for anything.

SELECT tournament_id, ROUND(100.0 * COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM registrations), 2) AS signup_pct
FROM registrations
GROUP BY tournament_id
ORDER BY signup_pct DESC, tournament_id;

What the interviewer is checking

Checks that you pick the right denominator, deduplicate the numerator, and know how your engine handles integer division.

Mistakes to avoid

• Counting rows instead of distinct players.
• Writing 100 instead of 100.0 and getting truncated percentages.
• Using registered players as the denominator instead of all players.
• Forgetting the tie-breaker on tournament_id.

Follow-ups to expect

• Include tournaments with zero registrations from a tournaments table (LEFT JOIN and COALESCE to 0).
• Report the rate per region (group players by region and divide by the region size).
• Show only tournaments above 50% (HAVING on the computed rate).

In real work

Feature adoption rates, event turnout and campaign reach are all "distinct participants over total population" with careful rounding.

Practice next