Abstract / Summary

Calculate the percentage of users who attended a contest by dividing attendees by total users and multiplying by 100.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 61% (900k+ submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The provided SQL statement computes the participation share of each contest relative to the total number of distinct users registered in the system. It aggregates registrations per contest, calculates a percentage, and orders the results to highlight the most popular contests first.

Key Steps

The query can be broken down into three logical phases: data aggregation, percentage calculation, and result ordering.

The first phase groups rows from the Register table by contest_id and counts the number of registrations per contest.

SELECT contest_id, COUNT(*) AS reg_count
FROM Register
GROUP BY contest_id

The second phase introduces a sub-query that determines the total number of unique users across the entire platform. This value is used as the denominator for the percentage computation. The ROUND function limits the output to two decimal places, ensuring a tidy presentation.

SELECT contest_id,
       ROUND(100 * COUNT(*) /
             (SELECT COUNT(DISTINCT user_id) FROM Users), 2) AS percentage
FROM Register
GROUP BY contest_id

The final phase sorts the derived rows first by the computed percentage in descending order, then by contest_id to provide a deterministic tie-break when percentages are equal.

ORDER BY percentage DESC, contest_id

Edge Cases Handled

When the Users table contains no rows, the sub-query returns NULL. In most SQL dialects, any arithmetic operation involving NULL yields NULL, causing the entire percentage column to be NULL. This behavior gracefully indicates that a meaningful participation metric cannot be derived without any users.

If a contest has no registrations, it does not appear in the result set because the GROUP BY clause only produces groups that contain at least one row. Consequently, contests with zero participation are omitted, which aligns with the intent to report only active contests.

The use of COUNT(DISTINCT user_id) ensures that duplicate user entries in the Users table do not inflate the denominator, preserving the integrity of the percentage calculation even if the user data contains accidental duplicates.

Performance Optimizations

The query leverages a scalar sub-query that is evaluated once, rather than recomputed for each row of the outer query. By placing the distinct user count in a separate sub-query, the database engine can cache the result, reducing redundant scans of the Users table.

The aggregation on the Register table is performed with a simple GROUP BY contest_id, which allows the optimizer to use an index on contest_id if one exists. This index can accelerate the grouping operation by avoiding a full table scan.

The final ORDER BY clause sorts on the computed percentage and then on contest_id. Because percentage is derived from the aggregation, the sorting occurs after the grouping phase, meaning the dataset being sorted is already reduced to one row per contest, minimizing the sorting workload.

Overall, the query is structured to minimize data movement: it aggregates first, computes the percentage using a pre-computed denominator, and then orders the compact result set. This sequence promotes efficient execution, especially on large datasets where the Register table may contain many rows but the number of distinct contests is comparatively small.

Imagine you have a big bucket full of blocks, and each block has a name written on it to show who owns it. There are lots of different contests happening at the same time, and kids are putting their blocks into different bins to join the fun. This code is like a helpful robot helper that walks around the room to see how popular each contest is.

First, the robot looks at all the contests and counts how many blocks are in each bin to figure out how many kids joined that specific game. Then, it runs over to the main roster to count every single kid in the whole building, making sure not to count the same person twice if they are just visiting. The robot takes the number of kids in a contest and divides it by the total number of kids in the building to see what piece of the pie that contest represents. It turns that fraction into a nice percentage number and rounds it off so it is easy to read. Finally, it sorts all the contests starting with the highest percentage at the very top of the list so everyone can see which game is the biggest hit. If two games have the same percentage, the robot just puts the one with the smaller number first to keep everything tidy.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

not analyzed yet

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL
	Progiez		C++	Java	Python
github	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
Progiez	C++, Java, Python
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality