1633. Percentage of Users Attended a Contest Solution

The problem

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.

Python

# Assume the availability of database access and SQL query execution using a library like sqlite3
import sqlite3

# Connect to the database
con = sqlite3.connect('database.db')

# Execute the SQL command
cur = con.cursor()
cur.execute('''
WITH TotalUsers AS (
  SELECT COUNT(DISTINCT user_id) AS total
  FROM Users
),
RegisteredUsers AS (
  SELECT contest_id, COUNT(DISTINCT user_id) AS registered
  FROM Register
  GROUP BY contest_id
)
SELECT 
  R.contest_id, 
  ROUND(100.0 * R.registered / T.total, 2) AS percentage
FROM RegisteredUsers R
JOIN TotalUsers T
ORDER BY percentage DESC, R.contest_id ASC;
''')

# Fetch results
result = cur.fetchall()
con.close()

# Display results
for row in result:
    print(row)

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.

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.

Try this approach in the editor

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.

JavaScript

const users = [ { user_id: 6, user_name: 'Alice' }, { user_id: 2, user_name: 'Bob' }, { user_id: 7, user_name: 'Alex' } ];
const 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 } ];

// Create a set to track unique users
const totalUsers = new Set(users.map(u => u.user_id)).size;

// Create a map to track users registered in each contest
const contestRegistrations = {};

register.forEach(({contest_id, user_id}) => {
  if (!contestRegistrations[contest_id]) contestRegistrations[contest_id] = new Set();
  contestRegistrations[contest_id].add(user_id);
});

// Calculate percentages and sort the results
const results = Object.entries(contestRegistrations).map(([contest_id, registrants]) => {
  return {
    contest_id: parseInt(contest_id),
    percentage: ((registrants.size / totalUsers) * 100).toFixed(2)
  };
}).sort((a, b) => b.percentage !== a.percentage ? b.percentage - a.percentage : a.contest_id - b.contest_id);

console.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.

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.

Try this approach in the editor

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.

MySQL

SELECT 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.

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.

Try this approach in the editor

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.

Python

import pandas as pd

def calculate_percentage(users, register):
    users_df = pd.DataFrame(users, columns=['user_id', 'user_name'])
    register_df = pd.DataFrame(register, columns=['contest_id', 'user_id'])
    total_users = users_df['user_id'].nunique()

    result = (
        register_df.groupby('contest_id')['user_id']
        .nunique()
        .reset_index(name='registered_users')
    )
    result['percentage'] = (result['registered_users'] / total_users) * 100
    result = result.round({'percentage': 2})

    result.sort_values(by=['percentage', 'contest_id'], ascending=[False, True], inplace=True)
    return result[['contest_id', 'percentage']]

# Example usage
users = [(6, 'Alice'), (2, 'Bob'), (7, 'Alex')]
register = [
    (215, 6), (209, 2), (208, 2), (210, 6), (208, 6),
    (209, 7), (209, 6), (215, 7), (208, 7), (210, 2),
    (207, 2), (210, 7)
]
print(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.

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.

Try this approach in the editor

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. Space Complexity: O(1) since we are using SQL joins and aggregations without requiring additional space proportional to input size.
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. Space Complexity: O(N + M) due to the use of sets and maps for storing data.
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.

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

Frequently asked questions

Is Percentage of Users Attended a Contest easy or hard?

Percentage of Users Attended a Contest is classified as an Easy database problem on LeetCode. The main challenge is understanding SQL aggregation and calculating percentages from grouped results. Once you know GROUP BY and COUNT, the solution is straightforward.

Percentage of Users Attended a Contest Python/Java solution

In Python or Java, iterate through the registration records and maintain a map from contest_id to registration count. Store the total number of users separately. After counting, compute percentage = (count / total_users) * 100, round to two decimals, and sort the results by percentage descending and contest id ascending.

How to solve Percentage of Users Attended a Contest in O(n)?

First compute the total number of users from the Users table. Then group the Register table by contest_id and count how many users registered for each contest. Calculate the percentage using COUNT(user_id) * 100 / total_users and round to two decimal places. Sorting by percentage descending and contest_id ascending produces the final result.

What is the best approach for Percentage of Users Attended a Contest?

The best approach uses SQL aggregation with GROUP BY on contest_id and a total user count from the Users table. Count the registered users per contest, divide by the total number of users, and multiply by 100 to compute the percentage. This runs in O(n) time because each registration row is processed once by the grouping operation.

Is Percentage of Users Attended a Contest asked at Google/Amazon/Meta?

Problems like this appear frequently in SQL interview rounds at companies such as Amazon, Google, and Meta. The question tests understanding of GROUP BY, aggregation functions, ratio calculations, and result ordering. Variants often appear in analytics or data engineer interviews.

What data structure is used in Percentage of Users Attended a Contest?

In SQL solutions, the database engine handles grouping internally using aggregation structures. In programmatic solutions, a hash map or dictionary is typically used to store counts of users per contest, while sets can track unique users for accurate percentage calculation.

What is the time complexity of Percentage of Users Attended a Contest?

The optimal solution runs in O(n) time where n is the number of rows in the Register table. The database performs a single grouping pass to count registrations per contest. Space complexity is O(1) in SQL because aggregation happens inside the query engine without additional structures in user code.

Percentage of Users Attended a Contest solution and explanation

The problem

Approach overview

Approach 1: Approach 1: SQL Aggregation and Join

Approach 2: Approach 2: Set Operations and In-Memory Calculation

Approach 3: Using SQL Queries

Approach 4: Using Programmatic Aggregation and Calculation

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Percentage of Users Attended a Contest solution and explanation

The problem

Approach overview

Approach 1: Approach 1: SQL Aggregation and Join

Approach 2: Approach 2: Set Operations and In-Memory Calculation

Approach 3: Using SQL Queries

Approach 4: Using Programmatic Aggregation and Calculation

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems