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%
In this approach, we will use SQL aggregation functions along with JOIN operations to compute the desired results:
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.
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.
In this approach, we will utilize in-memory operations and set data structures to calculate the percentage:
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.
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.
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.
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.
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.
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 | 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. |
Percentage of Users Attended a Contest | Leetcode 1633 | Crack SQL Interviews in 50 Qs #mysql • Learn With Chirag • 8,256 views views
Watch 9 more video solutions →Practice Percentage of Users Attended a Contest with our built-in code editor and test cases.
Practice on FleetCode