Knowee
Questions
Features
Study Tools

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%

Question

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%

...expand
🧐 Not the exact question you are looking for?Go ask a question

Solution

To solve this problem, we can use SQL queries. Here is the step-by-step solution:

  1. First, we need to count the total number of unique users. We can do this with the following SQL query:
SELECT COUNT(DISTINCT user_id) as total_users
FROM Users;
  1. Next, we need to count the number of users registered in each contest. We can do this with the following SQL query:
SELECT contest_id, COUNT(DISTINCT user_id) as contest_users
FROM Register
GROUP BY contest_id;
  1. Now, we need to calculate the percentage of users registered in each contest. We can do this by dividing the number of users in each contest by the total number of users and multiplying by 100. We can do this with the following SQL query:
SELECT contest_id, ROUND((contest_users / total_users) * 100, 2) as percentage
FROM (
    SELECT contest_id, COUNT(DISTINCT user_id) as contest_users
    FROM Register
    GROUP BY contest_id
) as contest_counts, (
    SELECT COUNT(DISTINCT user_id) as total_users
    FROM Users
) as total_counts;
  1. Finally, we need to order the result by percentage in descending order and by contest_id in ascending order in case of a tie. We can do this with the following SQL query:
SELECT contest_id, ROUND((contest_users / total_users) * 100, 2) as percentage
FROM (
    SELECT contest_id, COUNT(DISTINCT user_id) as contest_users
    FROM Register
    GROUP BY contest_id
) as contest_counts, (
    SELECT COUNT(DISTINCT user_id) as total_users
    FROM Users
) as total_counts
ORDER BY percentage DESC, contest_id ASC;

This will give us the desired output.

This problem has been solved

Similar Questions

How do you list all users in this table?+-------+-------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` ( `id` int(11) DEFAULT NULL, `name` varchar(256) DEFAULT NULL, `age` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-------------------------------------------------------------------------------------------------------------------------------+DELETE * FROM users;SELECT * FROM users;SELECT ALL users;

Write a SQL script that creates a table users following these requirements:With these attributes:id, integer, never null, auto increment and primary keyemail, string (255 characters), never null and uniquename, string (255 characters)If the table already exists, your script should not failYour script can be executed on any database

Identify the meaningful variable names which can be used?Select one or more:a.$register_numberb.user1c.user named.1num

A database administrator is setting up a new table that will contain customer information. The fields include name, address, company, phone, email, and customer ID. The administrator is using the customer ID to uniquely identify each customer record. Which of the following BEST describes the customer ID field?answerForeign keyPublic keyPrimary keyPrivate key

How do you delete the users record with id = 89 in this table?+-------+-------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` ( `id` int(11) DEFAULT NULL, `name` varchar(256) DEFAULT NULL, `age` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-------------------------------------------------------------------------------------------------------------------------------+DELETE users WHERE id = 89;DELETE FROM users WHERE id = 89;DELETE FROM users;DELETE FROM users WHERE id IS EQUAL TO 89;

1/1

Upgrade your grade with Knowee

Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.