Percentage of Users Attended a Contest - Problem

You are given two tables: Users and Register.

The Users table contains information about users with their user_id (primary key) and user_name.

The Register table contains contest registration data with contest_id and user_id as a composite primary key.

Task: Calculate the percentage of users who registered for each contest, rounded to 2 decimal places.

Output requirements:

  • Order results by percentage in descending order
  • For ties, order by contest_id in ascending order
  • Return contest_id and percentage columns

Table Schema

Users
Column Name Type Description
user_id PK int Unique identifier for each user
user_name varchar Name of the user
Primary Key: user_id
Register
Column Name Type Description
contest_id PK int Identifier for the contest
user_id PK int User who registered for the contest
Primary Key: (contest_id, user_id)

Input & Output

Example 1 — Basic Contest Registration
Input Tables:
Users
user_id user_name
1 Alice
2 Bob
3 Charlie
4 David
Register
contest_id user_id
208 1
208 2
210 2
Output:
contest_id percentage
208 50
210 25
💡 Note:

Contest 208 has 2 registrations out of 4 total users: (2/4) × 100 = 50.00%. Contest 210 has 1 registration out of 4 total users: (1/4) × 100 = 25.00%. Results are ordered by percentage descending.
Example 2 — Equal Percentages
Input Tables:
Users
user_id user_name
1 Alice
2 Bob
Register
contest_id user_id
215 1
220 2
Output:
contest_id percentage
215 50
220 50
💡 Note:

Both contests have the same percentage (50.00%), so they are ordered by contest_id in ascending order: 215 comes before 220.

Constraints

  • 1 ≤ user_id ≤ 1000
  • 1 ≤ contest_id ≤ 1000
  • Each user can register for multiple contests
  • Each (contest_id, user_id) pair is unique in Register table

Visualization

Tap to expand
Percentage of Users Attended a Contest INPUT Users Table user_id name 1 Alice 2 Bob 3 Carol Total Users = 3 Register Table contest_id user_id 215 1 215 2 215 3 209 1 209 2 208 1 ALGORITHM STEPS 1 Count Total Users SELECT COUNT(*) FROM Users Result: total_users = 3 2 Group by Contest COUNT registrations per contest_id 3 Calculate Percentage (count / total_users) * 100 ROUND to 2 decimals 4 Order Results By percentage DESC, then contest_id ASC SQL Query: SELECT contest_id, ROUND(COUNT(*)*100.0/ (SELECT COUNT(*) FROM Users), 2) AS pct FINAL RESULT contest_id percentage 215 100.00 209 66.67 208 33.33 Calculations: 215: 3/3 * 100 = 100.00% 209: 2/3 * 100 = 66.67% 208: 1/3 * 100 = 33.33% OK - Sorted by % DESC then contest_id ASC Contest Registration % 100% 66% 33% Key Insight: Use a subquery to get total user count, then divide each contest's registration count by this total. The ROUND function ensures 2 decimal places. ORDER BY handles ties with secondary sort on contest_id. This is an aggregation problem: GROUP BY contest_id with COUNT(*) for registrations per contest. TutorialsPoint - Percentage of Users Attended a Contest | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Facebook 6
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen