LeetCode 1934.确认值

数据准备

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');

需求

用户的 确认率 是 ‘confirmed’ 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。
编写一个SQL查询来查找每个用户的 确认率 。

输入

分析

-- if(action='confirmed',1,null) 判断是否确认,确认的赋值1,不是确认赋null,使用count求出人数
-- count(if(action='confirmed',1,null))/count(1) 确认的人数除以总人数
-- round(count(if(action='confirmed',1,null))/count(1),2) 保留两位小数
-- coalesce(round(count(if(action='confirmed',1,null))/count(1),2),0) 空值转换,如果为空,转为0

输出

-- if(action='confirmed',1,null) 判断是否确认,确认的赋值1,不是确认赋null,使用count求出人数
-- count(if(action='confirmed',1,null))/count(1) 确认的人数除以总人数
-- round(count(if(action='confirmed',1,null))/count(1),2) 保留两位小数
-- coalesce(round(count(if(action='confirmed',1,null))/count(1),2),0) 空值转换,如果为空,转为0
select s.user_id,
       coalesce(round(count(if(action='confirmed',1,null))/count(1),2),0) as confirmation_rate
from Signups s left join Confirmations c
on c.user_id=s.user_id
group by s.user_id;