Confirmation Rate
<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>&nbsp;</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 (&#39;confirmed&#39;, &#39;timeout&#39;)
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 (&#39;confirmed&#39;) or expired without confirming (&#39;timeout&#39;).
</pre>

<p>&nbsp;</p>

<p>The <strong>confirmation rate</strong> of a user is the number of <code>&#39;confirmed&#39;</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>&nbsp;</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>

[]
['Create table If Not Exists Signups (user_id int, time_stamp datetime)', "Create table If Not Exists Confirmations (user_id int, time_stamp datetime, action ENUM('confirmed','timeout'))", 'Truncate table Signups', "insert into Signups (user_id, time_stamp) values ('3', '2020-03-21 10:16:13')", "insert into Signups (user_id, time_stamp) values ('7', '2020-01-04 13:57:59')", "insert into Signups (user_id, time_stamp) values ('2', '2020-07-29 23:09:44')", "insert into Signups (user_id, time_stamp) values ('6', '2020-12-09 10:39:37')", 'Truncate table Confirmations', "insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-01-06 03:30:46', 'timeout')", "insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-07-14 14:00:00', 'timeout')", "insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-12 11:57:29', 'confirmed')", "insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-13 12:58:28', 'confirmed')", "insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-14 13:59:27', 'confirmed')", "insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-01-22 00:00:00', 'confirmed')", "insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-02-28 23:59:59', 'timeout')"]
[{"value": "mysql", "text": "MySQL", "defaultCode": "# Write your MySQL query statement below\n"}, {"value": "mssql", "text": "MS SQL Server", "defaultCode": "/* Write your T-SQL query statement below */\n"}, {"value": "oraclesql", "text": "Oracle", "defaultCode": "/* Write your PL/SQL query statement below */\n"}, {"value": "pythondata", "text": "Pandas", "defaultCode": "import pandas as pd\n\ndef confirmation_rate(signups: pd.DataFrame, confirmations: pd.DataFrame) -> pd.DataFrame:\n    "}, {"value": "postgresql", "text": "PostgreSQL", "defaultCode": "-- Write your PostgreSQL query statement below\n"}]
{"mysql": ["Create table If Not Exists Signups (user_id int, time_stamp datetime)", "Create table If Not Exists Confirmations (user_id int, time_stamp datetime, action ENUM('confirmed','timeout'))"], "mssql": ["Create table Signups (user_id int, time_stamp datetime)", "Create table Confirmations (user_id int, time_stamp datetime, action VARCHAR(10) NOT NULL CHECK (action IN ('confirmed','timeout')))"], "oraclesql": ["Create table Signups (user_id int, time_stamp date)", "Create table Confirmations (user_id int, time_stamp date, action VARCHAR(10) NOT NULL CHECK (action IN ('confirmed','timeout')))", "ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'"], "database": true, "name": "confirmation_rate", "pythondata": ["Signups = pd.DataFrame([], columns=['user_id', 'time_stamp']).astype({'user_id':'Int64', 'time_stamp':'datetime64[ns]'})", "Confirmations = pd.DataFrame([], columns=['user_id', 'time_stamp', 'action']).astype({'user_id':'Int64', 'time_stamp':'datetime64[ns]', 'action':'object'})"], "postgresql": ["Create table If Not Exists Signups (user_id int, time_stamp timestamp)\n", "Create table If Not Exists Confirmations (user_id int, time_stamp timestamp, action VARCHAR(30) CHECK (action IN ('confirmed','timeout')))\n"], "database_schema": {"Signups": {"user_id": "INT", "time_stamp": "DATETIME"}, "Confirmations": {"user_id": "INT", "time_stamp": "DATETIME", "action": "ENUM('confirmed', 'timeout')"}}}
{"mysql": ["MySQL", "<p><code>MySQL 8.0</code>.</p>"], "mssql": ["MS SQL Server", "<p><code>mssql server 2019</code>.</p>"], "oraclesql": ["Oracle", "<p><code>Oracle Sql 11.2</code>.</p>"], "pythondata": ["Pandas", "<p>Python 3.10 with Pandas 2.2.2 and NumPy 1.26.4</p>"], "postgresql": ["PostgreSQL", "<p>PostgreSQL 16</p>"]}
['Database']