一、背景说明
在这个竞争激烈的市场环境中，移动运营商不仅需要吸引新客户，还需要关注如何提高客户的留存率。保持客户持续使用套餐、提升客户粘性是衡量服务质量、客户满意度以及营销策略效果的重要指标。因此，分析客户首次办理套餐后的持续使用情况对于运营商是一个关键环节。
二、问题描述
现在有个业务场景需要分析用户连续登录行为，以便提供更好的用户粘性提升策略，请编写一个SQL查询，报告在用户首次登录后的第二天再次登录的用户比率，并将结果四舍五入到小数点后两位。 本次以梧桐数据库为例进行SQL实现及思路讲解。
三、表结构说明
梧桐数据库用户登录信息表建表语句
create table userlogins (
user_id int,
login_date date
);
四、表数据插入
通过insert语句向梧桐数据库插入样例数据
insert into userlogins
values (1, '2023-01-01'),
(1, '2023-01-02'),
(2, '2023-01-01'),
(3, '2023-01-02'),
(3, '2023-01-03');
数据插入后表结构如下：
| user_id | login_date |
|---|---|
| 1 | 2023-01-01 |
| 1 | 2023-01-02 |
| 2 | 2023-01-01 |
| 3 | 2023-01-02 |
| 3 | 2023-01-03 |
五、sql实现思路分解
1、定义首次登录日期 2、确定第二天登录的用户 3、通过主查询计算第二天登录比率，并四舍五入至小数点后两位
六、sql实现
-- 定义首次登录日期
with firstlogin as (
select
user_id,
min(login_date) as first_login_date -- 对于每个用户，选取最早的登录日期作为首次登录日期
from
userlogins -- 从用户登录信息表中读取数据
group by
user_id -- 按用户分组，确保每个用户的首次登录日期仅被计算一次
),
-- 确定第二天登录的用户
seconddaylogin as (
select
fl.user_id,
count(*) as second_day_count -- 统计第二天登录的次数
from
firstlogin fl
join
userlogins ul
on fl.user_id = ul.user_id and fl.first_login_date + INTERVAL 1 DAY = ul.login_date -- 将 userlogins 表与 firstlogin CTE 进行内连接，条件是用户的 user_id 相同，并且 login_date 是首次登录日期之后的一天
group by
fl.user_id -- 按用户分组，确保每个用户的第二天登录次数被正确统计
)
-- 计算第二天登录的比率
select
round(
sum(second_day_count) * 1.0 / count(distinct fl.user_id), 2
) as login_ratio -- 计算第二天登录比率，首先统计所有第二天登录的总次数 sum(second_day_count)，然后除以所有用户的数量 count(distinct fl.user_id)，最后使用 round 函数将结果保留两位小数
from
firstlogin fl
left join
seconddaylogin sdl on fl.user_id = sdl.user_id; -- 将 seconddaylogin CTE 与 firstlogin CTE 进行左连接，确保即使某些用户没有第二天登录记录也能出现在结果集中
七、sql每个部分的执行结果
定义首次登录日期
| user_id | first_login_date |
|---|---|
| 1 | 2023-01-01 |
| 3 | 2023-01-02 |
| 2 | 2023-01-01 |
确定第二天登录的用户
| user_id | second_day_count |
|---|---|
| 1 | 1 |
| 3 | 1 |
计算第二天登录的比率
| login_ratio |
|---|
| 0.67 |
