28. 查询有新注册用户的当天的新用户数量、新用户的第一天留存率
文章目录
- 题目需求
- 实现一
- 题目来源
题目需求
从用户登录明细表（user_login_detail）中查询有新注册用户的当天的新用户数量、新用户的第一天留存率。
首次登录算作当天新增，第二天也登录了算作一日留存。
期望结果如下：
| first_login （注册时间） | register （新增用户数） | retention |
|---|---|---|
| 2021-09-21 | 1 | 0.00 |
| 2021-09-22 | 1 | 0.00 |
| 2021-09-23 | 1 | 0.00 |
| … | … | … |
需要用到的表：
用户登录明细表：user_login_detail
| user_id(用户id) | ip_address(ip地址) | login_ts(登录时间) | logout_ts(登出时间) |
|---|---|---|---|
| 101 | 180.149.130.161 | 2021-09-21 08:00:00 | 2021-09-27 08:30:00 |
| 102 | 120.245.11.2 | 2021-09-22 09:00:00 | 2021-09-27 09:30:00 |
| 103 | 27.184.97.3 | 2021-09-23 10:00:00 | 2021-09-27 10:30:00 |
实现一
-- 4）计算出 每一天 新增用户的 留存率
select login_ts as first_login,register,-- 第二天活跃的用户数-- sum(whe),cast(sum(whe) / register as decimal(16, 2)) as retention
from (select t2.login_ts, -- 日期At2.register as register,t2.user_id,t3.day_all_user, -- 日期A+1，活跃的所有用户 user_id-- 判断该新增用户在 注册第二天是否活跃。1:活跃, 0: 不活跃if(array_contains(t3.day_all_user, t2.user_id), 1, 0) as whefrom (-- 2） 统计每个日期的新用户数量select login_ts,count(*) over (partition by login_ts) as register,user_idfrom (-- 1） 统计每个用户的首次登录日期select distinct user_id,first_value(date_format(login_ts, 'yyyy-MM-dd'))over (partition by user_id order by login_ts) as login_tsfrom user_login_detail) t1) t2left join(-- 3） 统计每个日期 活跃用户的user_idselect date_format(login_ts, 'yyyy-MM-dd') login_ts,collect_set(user_id) as day_all_userfrom user_login_detailgroup by date_format(login_ts, 'yyyy-MM-dd')) t3-- 日期A = 日期(B-1)。 B=A+1on t2.login_ts = date_add(t3.login_ts, -1)) t4
group by login_ts, register;
题目来源
http://practice.atguigu.cn/#/question/28/desc?qType=SQL
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！