Percentage of Users Attended a Contest - Solution & Explanation
Problem Statement
Table: Users
+-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | user_name | varchar | +-------------+---------+ user_id is the primary key (column with unique values) for this table. Each row of this table contains the name and the id of a user.
Table: Register
+-------------+---------+ | Column Name | Type | +-------------+---------+ | contest_id | int | | user_id | int | +-------------+---------+ (contest_id, user_id) is the primary key (combination of columns with unique values) for this table. Each row of this table contains the id of a user and the contest they registered into.
Write a solution to find the percentage of the users registered in each contest rounded to two decimals.
Return the result table ordered by percentage in descending order. In case of a tie, order it by contest_id in ascending order.
The result format is in the following example.
Example 1:
Input: Users table: +---------+-----------+ | user_id | user_name | +---------+-----------+ | 6 | Alice | | 2 | Bob | | 7 | Alex | +---------+-----------+ Register table: +------------+---------+ | 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 | percentage | +------------+------------+ | 208 | 100.0 | | 209 | 100.0 | | 210 | 100.0 | | 215 | 66.67 | | 207 | 33.33 | +------------+------------+ Explanation: All the users registered in contests 208, 209, and 210. The percentage is 100% and we sort them in the answer table by contest_id in ascending order. Alice and Alex registered in contest 215 and the percentage is ((2/3) * 100) = 66.67% Bob registered in contest 207 and the percentage is ((1/3) * 100) = 33.33%
Approach Overview
Problem Overview: You are given a Users table and a Register table that records which users registered for which contest. For each contest, compute the percentage of total users who registered. The result must be rounded to two decimals and sorted by percentage (descending) and contest id (ascending).
Approach 1: SQL Aggregation and Join (O(n) time, O(1) extra space)
The most direct solution uses SQL aggregation. First determine the total number of users from the Users table. Then group rows in Register by contest_id and count how many users registered for each contest. The percentage is calculated using COUNT(user_id) * 100 / total_users. Sorting is handled using ORDER BY percentage DESC, contest_id ASC. This approach relies on database-level aggregation and works efficiently because grouping and counting are optimized operations in relational engines. This pattern frequently appears in SQL interview questions that require computing ratios from grouped data.
Approach 2: Set Operations and In-Memory Calculation (O(n) time, O(n) space)
If the data is processed outside the database (for example in JavaScript), load both tables and simulate the grouping logic in memory. Store all unique users in a set to determine the total population. Then iterate through the registration records and maintain a map where the key is contest_id and the value is a set of registered users. After processing all rows, compute the percentage for each contest using (registered_users / total_users) * 100. This approach mirrors the SQL logic but uses sets and hash maps instead of database grouping. It’s a common pattern when solving hash table aggregation problems.
Approach 3: Using SQL Queries (O(n) time, O(1) extra space)
A concise SQL-only solution calculates the total user count with a subquery and performs grouping directly on the registration table. The query structure looks like: SELECT contest_id, ROUND(COUNT(user_id) * 100 / (SELECT COUNT(*) FROM Users), 2) with a GROUP BY contest_id. Modern databases evaluate the subquery once, so the total user count is reused across groups. This keeps the query readable while avoiding additional joins. Problems tagged under database often favor this approach because it minimizes query complexity.
Approach 4: Programmatic Aggregation and Calculation (O(n) time, O(n) space)
In Python or another backend language, iterate through the registration list and build a dictionary that counts registrations per contest. Store the total number of users separately. After counting, iterate over the dictionary and compute percentages using floating-point arithmetic, then round to two decimal places. Finally, sort the result list using a custom comparator that prioritizes percentage descending and contest id ascending. This approach is useful when contest data is already loaded into application memory or when the task appears inside a larger data-processing pipeline.
Recommended for interviews: The SQL aggregation approach is what interviewers usually expect for a database problem. It shows you understand GROUP BY, aggregation functions, and how to compute ratios from grouped data. Implementing the same logic with sets or hash maps demonstrates that you understand the underlying mechanics beyond SQL syntax.
Approach 1: Approach 1: SQL Aggregation and Join
In this approach, we will use SQL aggregation functions along with JOIN operations to compute the desired results:
- Calculate the total number of users from the Users table.
- Join the Register table with the Users table and group by contest_id. Count the unique users registered for each contest.
- Calculate the percentage of users registered for each contest by dividing the count from step 2 by the total users count and multiplying by 100.
- Order the results by percentage in descending order and contest_id in ascending order.
This Python code uses SQL queries to retrieve the required percentage of users registered in each contest from a database. It uses CTE (Common Table Expression) for calculating total users and registered users per contest, then computes the percentage and orders the result as specified in the problem.
Code
Python
Complexity
Time Complexity: O(N + M), where N is the number of rows in the Users table and M is the number of rows in the Register table.
Space Complexity: O(1) since we are using SQL joins and aggregations without requiring additional space proportional to input size.
Approach 2: Approach 2: Set Operations and In-Memory Calculation
In this approach, we will utilize in-memory operations and set data structures to calculate the percentage:
- Fetch all users from the Users table into a set data structure to get the total number of unique users.
- Fetch contest registrations from the Register table and use a dictionary to map each contest_id to a set of unique users who registered for that contest.
- Calculate registration percentages by dividing the size of each contest's user set by the total number of users.
- Sort results based on percentage and contest_id as per the problem requirements.
This JavaScript code uses in-memory objects and arrays to calculate the registrations and percentage of participation by users for each contest. It leverages JavaScript’s Set for ensuring unique user counts and the Array sort method to order the results as described in the problem statement.
Code
JavaScript
Complexity
Time Complexity: O(N + M), where N is the number of users and M is the number of contest registrations.
Space Complexity: O(N + M) due to the use of sets and maps for storing data.
Approach 3: Using SQL Queries
This approach leverages SQL queries to solve the problem directly against the database tables.
We will use a combination of the COUNT DISTINCT and GROUP BY operations to count the number of unique users per contest. Additionally, we will calculate the percentage with respect to the total number of users, and then order by the percentage in descending order and by contest_id ascending in case of ties.
The solution here uses a GROUP BY clause to group all entries in the Register table by contest_id, and then it applies a COUNT(DISTINCT user_id) to find unique users per contest.
We divide this count by the total number of users (using a subquery SELECT COUNT(*) FROM Users) to get the percentage of users who participated in each contest. This percentage is rounded to two decimals using ROUND().
The ORDER BY clause ensures that the results are presented ordered by percentage descending and contest_id ascending in the case of ties.
Code
SQL
Complexity
Time Complexity: O(N + M), where N is the number of rows in the Register table and M is the number of rows in the Users table due to counting operations.
Space Complexity: O(1), as the space used does not depend on the size of the input but is instead used for storing results and intermediate computation within SQL's processing environment.
Approach 4: Using Programmatic Aggregation and Calculation
This approach involves pulling the data into a programming environment and using data structures and algorithms to calculate the required percentages and orders programmatically.
We will create mappings from contest_id to the number of unique users registered, compute the percentage against the total number of users, and then order results appropriately.
In this Python solution, we use pandas for data manipulation. We load the user and register data into DataFrames for ease of use.
We employ groupby to group the data by contest_id and calculate the number of unique users per contest with the nunique function.
We calculate the percentage by dividing the number of registered users per contest by the total unique users and multiplying by 100. The result is rounded to two decimal places.
Finally, we sort the DataFrame by percentage (descending) and contest_id (ascending), returning a DataFrame with the contest_id and calculated percentage.
Code
Python
Complexity
Time Complexity: O(N + M), where N is the number of unique user_id entries in the Register table, and M is the number of user_id entries in the Users table for calculating unique counts.
Space Complexity: O(N + M), arising from creating DataFrame structures for both tables.
Approach 5: Grouping and Subquery
We can group the Register table by contest_id and count the number of registrations for each contest. The registration rate of each contest is the number of registrations divided by the total number of registrations.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| Approach 1: SQL Aggregation and Join | Time Complexity: O(N + M), where N is the number of rows in the Users table and M is the number of rows in the Register table. |
| Approach 2: Set Operations and In-Memory Calculation | Time Complexity: O(N + M), where N is the number of users and M is the number of contest registrations. |
| Using SQL Queries | Time Complexity: O(N + M), where N is the number of rows in the Register table and M is the number of rows in the Users table due to counting operations. Space Complexity: O(1), as the space used does not depend on the size of the input but is instead used for storing results and intermediate computation within SQL's processing environment. |
| Using Programmatic Aggregation and Calculation | Time Complexity: O(N + M), where N is the number of unique user_id entries in the Register table, and M is the number of user_id entries in the Users table for calculating unique counts. Space Complexity: O(N + M), arising from creating DataFrame structures for both tables. |
| Grouping and Subquery | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Aggregation and Join | O(n) | O(1) | Best choice for database interviews where aggregation and grouping are required |
| Set Operations and In-Memory Calculation | O(n) | O(n) | When data is processed in application code rather than SQL |
| Pure SQL Query with Subquery | O(n) | O(1) | Concise SQL solution when a single query is preferred |
| Programmatic Aggregation (Python) | O(n) | O(n) | Useful for backend processing pipelines or coding interview practice outside SQL |
Video Solution
Percentage of Users Attended a Contest | Leetcode 1633 | Crack SQL Interviews in 50 Qs #mysql • Learn With Chirag • 17,151 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Percentage of Users Attended a Contest easy or hard?
Percentage of Users Attended a Contest Python/Java solution
How to solve Percentage of Users Attended a Contest in O(n)?
What is the best approach for Percentage of Users Attended a Contest?
Is Percentage of Users Attended a Contest asked at Google/Amazon/Meta?
What data structure is used in Percentage of Users Attended a Contest?
What is the time complexity of Percentage of Users Attended a Contest?
Ready to solve this problem?
Practice Percentage of Users Attended a Contest with our built-in code editor and test cases.
Practice on FleetCode