Leecode_SQ50_1934. Confirmation Rate

最新推荐文章于 2025-12-15 21:10:21 发布

原创最新推荐文章于 2025-12-15 21:10:21 发布 · 700 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

SQL笔记专栏收录该内容

18 篇文章

订阅专栏

Leecode
没做出来

Confirmation Rate

Problem description

Table: Signups

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.

Example 1:

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

My failed solution

Because there is no OUTER JOIN in MySQL…

WITH ti AS (
    SELECT COUNT(action) AS c_ti
    FROM Confirmations c
        WHERE action = 'timeout'
    GROUP BY user_id
), 
co AS (
    SELECT COUNT(action) AS c_co
    FROM Confirmations c
        WHERE action = 'confirmed'
    GROUP BY user_id
), 

rate AS (
    SELECT co.user_id, ROUND((co.c_co / ti.c_ti), 2) AS confirmation_rate
    FROM ti
    OUTER JOIN co
        ON ti.user_id = co.user_id
)

SELECT s.user_id, rate.confirmation_rate
FROM Signups s
    LEFT JOIN rate
        ON s.user_id = rate.user_id

Example answer

# Write your MySQL query statement below
SELECT s.user_id, ROUND(AVG(IF(c.action="confirmed", 1, 0)), 2) AS confirmation_rate
FROM Signups s 
	LEFT JOIN Confirmations c
		ON s.user_id=c.user_id
group by user_id;

AVG(IF(c.action="confirmed", 1, 0))

是说，如果你想计算某个条件下的平均值，可以使用 CASE 语句结合 AVG() 函数。想要计算当 c.action 等于 “confirmed” 时的平均值，其中 “confirmed” 计为 1，否则计为 0。
还可以写成

AVG(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END)