1934. Confirmation Rate

While I was trying to solve below SQL question on LeetCode, I encountered an unfamiliar issue. In order to solve the problem, I attempted to use 'case when' to calculate the counts of 'timeout' and 'confirmed', and then utilize the sum to derive the value of 'confirmation_Rate'. However, despite multiple attempts, I couldn't solve it due to errors in the code.

 

 

---

 

Table: Signups

+----------------+----------+
| 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.

 

 

Table: Confirmations

+----------------+----------+
| 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').

 

 

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write a solution to find the confirmation rate of each user.

Return the result table in any order.

The result format is in the following example.

 

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   |
+---------+---------------------+-----------+
Output: 
+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6       | 0.00              |
| 3       | 0.00              |
| 7       | 1.00              |
| 2       | 0.50              |
+---------+-------------------+

 

 

Explanation: 
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.

 

---

 

I tried to solve this question by using 'case when' to assign and count each single value

 

SELECT 
    COUNT(CASE WHEN action = 'confirm' THEN 1 END) AS confirm_count,
    COUNT(CASE WHEN action = 'timeout' THEN 1 END) AS timeout_count,
    COUNT(CASE WHEN action IN ('confirm', 'timeout') THEN 1 END) AS total_count,
    COUNT(CASE WHEN action = 'confirm' THEN 1 END) / NULLIF(COUNT(CASE WHEN action IN ('confirm', 'timeout') THEN 1 END), 0) AS confirm_to_total_ratio
FROM 
    Signups s
LEFT JOIN 
    Confirmations c ON s.user_id = c.user_id

 

 

I couldn't solve it using this method. The reason was that I needed to calculate the sum for each group and then divide it, and errors occurred when null values were encountered. I had no choice but to search on Google to find a new approach.

 

SELECT 
    s.user_id,
    ROUND(
    	COALESCE(
        	CAST(
            	SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END) AS DECIMAL) / NULLIF(COUNT(c.user_id), 0), 0), 2) AS confirmation_rate
FROM 
    Signups s
LEFT JOIN 
    Confirmations c ON s.user_id = c.user_id
GROUP BY 
    s.user_id;

 

After I was studing about this code, I was wondering what was the reason to use 'Coalesce' and 'Cast'

 

here is my note 

 

The reason for using COALESCE is:

1. Handling NULL values: In database queries, there could be instances where the result might contain NULL values. Using COALESCE ensures that NULL values are replaced with another specified value, maintaining consistency in the query result and facilitating easier handling of the data.
2. Preventing division by zero errors: When performing division operations, dividing by zero typically results in an error. By using COALESCE, we can return NULL if the denominator is zero, allowing us to handle NULL values subsequently.

In this query, COALESCE is used to return NULL if the denominator is zero, preventing division by zero errors and enabling consistent handling of the query result.

 

The reason for using CAST is:

 

To ensure that the division operation yields a decimal or floating-point result, rather than an integer result.