Sponsored
Sponsored
In this approach, we will use SQL aggregation functions along with JOIN operations to compute the desired results:
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.
1# Assume the availability of database access and SQL query execution using a library like sqlite3
2import sqlite3
3
4
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.
In this approach, we will utilize in-memory operations and set data structures to calculate the percentage:
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.
1const users = [ { user_id: 6, user_name: 'Alice' }, { user_id: 2, user_name: 'Bob' }, { user_id: 7, user_name: 'Alex' } ];
2const register = [ { contest_id: 215, user_id: 6 }, { contest_id: 209, user_id: 2 }, { contest_id: 208, user_id: 2 }, { contest_id: 210, user_id: 6 }, { contest_id: 208, user_id: 6 }, { contest_id: 209, user_id: 7 }, { contest_id: 209, user_id: 6 }, { contest_id: 215, user_id: 7 }, { contest_id: 208, user_id: 7 }, { contest_id: 210, user_id: 2 }, { contest_id: 207, user_id: 2 }, { contest_id: 210, user_id: 7 } ];
3
4// Create a set to track unique users
5const totalUsers = new Set(users.map(u => u.user_id)).size;
6
7// Create a map to track users registered in each contest
8const contestRegistrations = {};
9
10register.forEach(({contest_id, user_id}) => {
11 if (!contestRegistrations[contest_id]) contestRegistrations[contest_id] = new Set();
12 contestRegistrations[contest_id].add(user_id);
13});
14
15// Calculate percentages and sort the results
16const results = Object.entries(contestRegistrations).map(([contest_id, registrants]) => {
17 return {
18 contest_id: parseInt(contest_id),
19 percentage: ((registrants.size / totalUsers) * 100).toFixed(2)
20 };
21}).sort((a, b) => b.percentage !== a.percentage ? b.percentage - a.percentage : a.contest_id - b.contest_id);
22
23console.log(results);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.
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.
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.
1SELECT contest_id, ROUND(COUNT(DISTINCT user_id) * 100.0 / (SELECT COUNT(*) FROM Users), 2) AS percentage FROM Register GROUP BY contest_id ORDER BY percentage DESC, contest_id ASC;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.
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.
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.
1import pandas as pd
2
3def calculate_percentage(users, register):
4 users_df = pd.DataFrame(users, columns=['user_id', 'user_name'])
5 register_df = pd.DataFrame(register, columns=['contest_id', 'user_id'])
6 total_users = users_df['user_id'].nunique()
7
8 result = (
9 register_df.groupby('contest_id')['user_id']
10 .nunique()
11 .reset_index(name='registered_users')
12 )
13 result['percentage'] = (result['registered_users'] / total_users) * 100
14 result = result.round({'percentage': 2})
15
16 result.sort_values(by=['percentage', 'contest_id'], ascending=[False, True], inplace=True)
17 return result[['contest_id', 'percentage']]
18
19# Example usage
20users = [(6, 'Alice'), (2, 'Bob'), (7, 'Alex')]
21register = [
22 (215, 6), (209, 2), (208, 2), (210, 6), (208, 6),
23 (209, 7), (209, 6), (215, 7), (208, 7), (210, 2),
24 (207, 2), (210, 7)
25]
26print(calculate_percentage(users, register))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.