Watch 10 video solutions for Percentage of Users Attended a Contest, a easy level problem involving Database. This walkthrough by Learn With Chirag has 15,526 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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%
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 | 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 |
