mirror of
https://gitee.com/coder-xiaomo/leetcode-problemset
synced 2025-01-10 18:48:13 +08:00
83 lines
3.2 KiB
HTML
83 lines
3.2 KiB
HTML
<p>Table: <code>Signups</code></p>
|
|
|
|
<pre>
|
|
+----------------+----------+
|
|
| Column Name | Type |
|
|
+----------------+----------+
|
|
| user_id | int |
|
|
| time_stamp | datetime |
|
|
+----------------+----------+
|
|
user_id is the column of unique values for this table.
|
|
Each row contains information about the signup time for the user with ID user_id.
|
|
</pre>
|
|
|
|
<p> </p>
|
|
|
|
<p>Table: <code>Confirmations</code></p>
|
|
|
|
<pre>
|
|
+----------------+----------+
|
|
| Column Name | Type |
|
|
+----------------+----------+
|
|
| user_id | int |
|
|
| time_stamp | datetime |
|
|
| action | ENUM |
|
|
+----------------+----------+
|
|
(user_id, time_stamp) is the primary key (combination of columns with unique values) for this table.
|
|
user_id is a foreign key (reference column) to the Signups table.
|
|
action is an ENUM (category) of the type ('confirmed', 'timeout')
|
|
Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').
|
|
</pre>
|
|
|
|
<p> </p>
|
|
|
|
<p>The <strong>confirmation rate</strong> of a user is the number of <code>'confirmed'</code> messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is <code>0</code>. Round the confirmation rate to <strong>two decimal</strong> places.</p>
|
|
|
|
<p>Write a solution to find the <strong>confirmation rate</strong> of each user.</p>
|
|
|
|
<p>Return the result table in <strong>any order</strong>.</p>
|
|
|
|
<p>The result format is in the following example.</p>
|
|
|
|
<p> </p>
|
|
<p><strong class="example">Example 1:</strong></p>
|
|
|
|
<pre>
|
|
<strong>Input:</strong>
|
|
Signups table:
|
|
+---------+---------------------+
|
|
| user_id | time_stamp |
|
|
+---------+---------------------+
|
|
| 3 | 2020-03-21 10:16:13 |
|
|
| 7 | 2020-01-04 13:57:59 |
|
|
| 2 | 2020-07-29 23:09:44 |
|
|
| 6 | 2020-12-09 10:39:37 |
|
|
+---------+---------------------+
|
|
Confirmations table:
|
|
+---------+---------------------+-----------+
|
|
| user_id | time_stamp | action |
|
|
+---------+---------------------+-----------+
|
|
| 3 | 2021-01-06 03:30:46 | timeout |
|
|
| 3 | 2021-07-14 14:00:00 | timeout |
|
|
| 7 | 2021-06-12 11:57:29 | confirmed |
|
|
| 7 | 2021-06-13 12:58:28 | confirmed |
|
|
| 7 | 2021-06-14 13:59:27 | confirmed |
|
|
| 2 | 2021-01-22 00:00:00 | confirmed |
|
|
| 2 | 2021-02-28 23:59:59 | timeout |
|
|
+---------+---------------------+-----------+
|
|
<strong>Output:</strong>
|
|
+---------+-------------------+
|
|
| user_id | confirmation_rate |
|
|
+---------+-------------------+
|
|
| 6 | 0.00 |
|
|
| 3 | 0.00 |
|
|
| 7 | 1.00 |
|
|
| 2 | 0.50 |
|
|
+---------+-------------------+
|
|
<strong>Explanation:</strong>
|
|
User 6 did not request any confirmation messages. The confirmation rate is 0.
|
|
User 3 made 2 requests and both timed out. The confirmation rate is 0.
|
|
User 7 made 3 requests and all were confirmed. The confirmation rate is 1.
|
|
User 2 made 2 requests where one was confirmed and the other timed out. The confirmation rate is 1 / 2 = 0.5.
|
|
</pre>
|