Percentage of Users Attended a Contest | Leetcode 1633

1. Overview and Problem Statement

This chapter covers problem number 18 from the SQL interview series: Percentage of Users Attended a Contest (LeetCode 1633). The goal of this problem is to calculate the percentage of total users who registered for each individual contest, round the result to two decimal places, and sort the output systematically.

• Primary Input Tables: Users and Register.
• Primary Objective: Compute participation percentage per contest (contest_id).
• Sorting Requirement: Descending order by percentage, and in case of a tie, ascending order by contest_id.

2. Understanding the Schema

We are provided with two tables containing user demographics and contest registration details:

Table 1: Users

• Columns: user_id, user_name
• Primary Key: user_id
• Description: Each row contains the unique ID and name of a registered user in the system.

Table 2: Register

• Columns: contest_id, user_id
• Primary Key: (contest_id, user_id) (Composite Primary Key)
• Description: Each row indicates that a specific user_id has registered for a given contest_id.

3. Real-Life Analogy: Club Membership Event Attendance

Real-Life Analogy: Imagine a university club with a total roster of 3 active members (User A, User B, and User C). The club organizes multiple workshops throughout the semester.

When Workshop #215 takes place, Member A and Member B attend. To find out what percentage of the total club membership attended Workshop #215, we take the number of attendees present (2 members), divide it by the total club roster size (3 members), and multiply by 100 to get 66.67%. If all 3 members show up to Workshop #208, the attendance rate is 3 / 3 * 100 = 100%.

4. Step-by-Step Mathematical Formulation

To calculate the percentage of users who attended each contest, the formula used for every contest_id is:

Example Walkthrough:

Assume there are 3 total users in the Users table (user_id: 2, 6, 7).

1. Contest 215:

   • Registered users in Register table: user_id = 6 and user_id = 7 (Total = 2 unique users).
   • Calculation: $\left(\frac{2}{3}\right) \times 100 = 66.6666...\%$
   • Rounded to 2 decimal places: 66.67

2. Contest 208:

   • Registered users in Register table: user_id = 2, user_id = 6, and user_id = 7 (Total = 3 unique users).
   • Calculation: $\left(\frac{3}{3}\right) \times 100 = 100.00\%$
   • Rounded to 2 decimal places: 100.00

5. Naive Approach vs. Production-Ready SQL Query

Naive / Flawed SQL Approach

A beginner might attempt to calculate the percentage by joining tables directly without considering aggregate grouping or scalar subqueries for the total user count:

-- NAIVE / FLAWED APPROACH (DO NOT USE)
SELECT 
    contest_id, 
    COUNT(user_id) * 100 / COUNT(user_id) AS percentage
FROM Register r
JOIN Users u ON r.user_id = u.user_id
GROUP BY contest_id;

Note: The naive approach fails because joining the tables before aggregation distorts the row counts, and it fails to factor in the total global count of users who did not register for any contest.

Production-Ready SQL Solution

To write a robust, optimized query, we aggregate the registrations per contest and divide by the total count of users retrieved dynamically from the Users table via a subquery.

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;

6. Complete Flow Walkthrough of the Query Execution

7. Key SQL Functions & Operators Explained

• COUNT(DISTINCT user_id): Ensures that even if duplicate registration records exist for a user in a single contest, each user is only counted once.
• 100.0 (Implicit Type Casting): Multiplying by 100.0 instead of integer 100 forces SQL to perform floating-point division rather than integer division (which would truncate decimals like 66.67 down to 0).
• (SELECT COUNT(*) FROM Users): A scalar subquery that computes the dynamic total population of users against which every contest's turnout is measured.
• ROUND(expression, 2): Truncates and rounds the final numerical percentage to exactly two decimal places.
• ORDER BY percentage DESC, contest_id ASC: Fulfills sorting constraints, listing highest participation percentages first, with tie-breakers settled by ascending contest_id.

8. Summary and Key Takeaways

• Understand Table Relationships: Recognize how composite primary keys (contest_id, user_id) function in relational schemas.
• Use COUNT(DISTINCT ...): Always eliminate duplicate entries when counting unique participants in event logs.
• Prevent Integer Division Truncation: Multiply counts by floating-point literals (100.0) to preserve decimal precision in SQL databases.
• Dynamic Denominators: Utilize subqueries like (SELECT COUNT(*) FROM Users) to compute ratios against the entire system population rather than hardcoding values.
• Rounding Output: Apply the ROUND(val, 2) function to format floating-point ratios cleanly for business reporting.
• Multi-Level Sorting: Combine DESC and ASC clauses in ORDER BY statements to handle primary metrics and tie-breaking rules gracefully.