$1633.$ Percentage of Users Attended a Contest

Easy

Topics

Companies

SQL Schema

Pandas Schema

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\mathrm{.}0|$

$|209|100\mathrm{.}0|$

$|210|100\mathrm{.}0|$

$|215|66\mathrm{.}67|$

$|207|33\mathrm{.}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\mathrm{.}67\%$

Bob registered in contest $207$ and the percentage is $((1/3)*100)=33\mathrm{.}33\%$