Contest Participation Rate by Event

Easy

A community learning platform hosts many coding contests and tracks which users sign up for each event. Table: users user_id: unique platform user identifier user_name: user display name Table: register contest_id: contest identifier user_id: user who registered for that contest Task: For every contest_id present in register, compute what share of all platform users registered for that contest. Metric: percentage = (attendee_count / total_users) * 100 attendee_count is the number of rows for a contest in register total_users is the number of rows in users Output requirements: Return exactly two columns: contest_id, percentage Round percentage to 2 decimal places Sort by percentage in descending order If two contests have the same percentage, sort by contest_id in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

users:
| user_id | user_name |
|---------|-----------|
| 6       | Alice     |
| 2       | Bob       |
| 7       | Alex      |

register:
| contest_id | user_id |
|------------|---------|
| 215        | 6       |
| 209        | 2       |
| 208        | 2       |
| 210        | 6       |
| 208        | 6       |
| 209        | 7       |
| 209        | 6       |
| 215        | 7       |
| 208        | 7       |
| 210        | 2       |
| 207        | 2       |
| 210        | 7       |

Output

[
  {"contest_id":208,"percentage":100.0},
  {"contest_id":209,"percentage":100.0},
  {"contest_id":210,"percentage":100.0},
  {"contest_id":215,"percentage":66.67},
  {"contest_id":207,"percentage":33.33}
]

Explanation

Contests 208, 209, and 210 have all 3 users, so each is 100.00. Contest 215 has 2 of 3 users (66.67), and contest 207 has 1 of 3 users (33.33).

Example 2

Input

users:
| user_id | user_name |
|---------|-----------|
| 1       | Maya      |
| 2       | Noah      |
| 3       | Pia       |
| 4       | Rohan     |

register:
| contest_id | user_id |
|------------|---------|
| 501        | 1       |
| 501        | 2       |
| 501        | 3       |
| 502        | 2       |
| 502        | 4       |
| 503        | 1       |
| 503        | 3       |

Output

[
  {"contest_id":501,"percentage":75.0},
  {"contest_id":502,"percentage":50.0},
  {"contest_id":503,"percentage":50.0}
]

Explanation

Contest 501 has 3 of 4 users (75.00). Contests 502 and 503 each have 2 of 4 users (50.00), so tie-breaking uses contest_id ascending.

Example 3

Input

users:
| user_id | user_name |
|---------|-----------|
| 10      | Elena     |
| 11      | Faris     |
| 12      | Gia       |

register:
[]

Output

[]

Explanation

No contest appears in register, so there are no output rows.

Constraints

1 <= number of rows in users <= 200000
0 <= number of rows in register <= 500000
users.user_id is unique
(register.contest_id, register.user_id) is unique
1 <= users.user_id <= 2147483647
1 <= register.contest_id <= 2147483647
register.user_id values belong to existing users
Return exactly two columns: contest_id, percentage
Round percentage to exactly 2 decimals
Sort by percentage DESC, then contest_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns contest_id and percentage

users: | user_id | user_name | |---------|-----------| | 6 | Alice | | 2 | Bob | | 7 | Alex | register: | contest_id | user_id | |------------|---------| | 215 | 6 | | 209 | 2 | | 208 | 2 | | 210 | 6 | | 208 | 6 | | 209 | 7 | | 209 | 6 | | 215 | 7 | | 208 | 7 | | 210 | 2 | | 207 | 2 | | 210 | 7 |

[ {"contest_id":208,"percentage":100.0}, {"contest_id":209,"percentage":100.0}, {"contest_id":210,"percentage":100.0}, {"contest_id":215,"percentage":66.67}, {"contest_id":207,"percentage":33.33} ]

users: | user_id | user_name | |---------|-----------| | 1 | Maya | | 2 | Noah | | 3 | Pia | | 4 | Rohan | register: | contest_id | user_id | |------------|---------| | 501 | 1 | | 501 | 2 | | 501 | 3 | | 502 | 2 | | 502 | 4 | | 503 | 1 | | 503 | 3 |