sql常考题

梧桐林.

已于 2022-02-26 10:03:30 修改

阅读量337

点赞数

分类专栏： sql 文章标签：用户留存 SQL 数据分析用户活跃日志分析

于 2022-02-19 18:03:55 首次发布

本文链接：https://blog.csdn.net/m0_46191714/article/details/123021677

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

留存率问题

牛客上的原题https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450?tpId=268&tqId=2285344&ru=/exam/oj&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Foj%3Ftab%3DSQL%25E7%25AF%2587%26topicId%3D268
解答

select a.dt,
       round(count(b.uid)/ count(a.uid),2) as uv_left_rate
from (select uid
      ,min(date(in_time)) dt
      from tb_user_log
      group by uid) as a
left join (select uid , date(in_time) dt
           from tb_user_log
           union
           select uid , date(out_time)
           from tb_user_log) as b 
on a.uid=b.uid
and a.dt=date_sub(b.dt,INTERVAL 1 day)
where date_format(a.dt,"%Y-%m")='2021-11'
group by a.dt

进阶：

/*计算某日新增登录设备的次日、3日、7日、14日、30日、90日留存率*/
SELECT
	log_day '日期',
	count(user_id_d0) '新增数量',
	count(user_id_d1) / count(user_id_d0) '次日留存',
	count(user_id_d3) / count(user_id_d0) '3日留存',
	count(user_id_d7) / count(user_id_d0) '7日留存',
	count(user_id_d14) / count(user_id_d0) '14日留存',
	count(user_id_d30) / count(user_id_d0) '30日留存',
	count(user_id_d90) / count(user_id_d0) '90日留存'
FROM
	(
		SELECT DISTINCT
			log_day,
			a.user_id_d0,
			b.device_id AS user_id_d1,
			c.device_id AS user_id_d3,
			d.device_id AS user_id_d7,
			e.device_id AS user_id_d14,
			f.device_id AS user_id_d30,
			g.device_id AS user_id_d90
		FROM
			(
				SELECT DISTINCT
					Date(event_time) AS log_day,
					device_id AS user_id_d0
				FROM
					role_login_back
				GROUP BY
					device_id
				ORDER BY
					log_day
			) a
		LEFT JOIN role_login_back b ON DATEDIFF(DATE(b.event_time),a.log_day) = 1
		AND a.user_id_d0 = b.device_id
		LEFT JOIN role_login_back c ON DATEDIFF(DATE(c.event_time),a.log_day) = 2
		AND a.user_id_d0 = c.device_id
		LEFT JOIN role_login_back d ON DATEDIFF(DATE(d.event_time),a.log_day) = 6
		AND a.user_id_d0 = d.device_id
		LEFT JOIN role_login_back e ON DATEDIFF(DATE(e.event_time),a.log_day) = 13
		AND a.user_id_d0 = e.device_id
		LEFT JOIN role_login_back f ON DATEDIFF(DATE(f.event_time),a.log_day) = 29
		AND a.user_id_d0 = f.device_id
		LEFT JOIN role_login_back g ON DATEDIFF(DATE(g.event_time),a.log_day) = 89
		AND a.user_id_d0 = g.device_id
	) AS temp
GROUP BY
log_day

原文：https://zhuanlan.zhihu.com/p/143494489

3.6.1 如何分析用户活跃？

在启动日志中统计不同设备id出现次数。去重

3.6.2 如何分析用户新增？vivo

用活跃用户表 left join 用户新增表，用户新增表中mid为空的即为用户新增。

3.6.3 如何分析用户1天留存？

留存用户=前一天新增 join 今天活跃
用户留存率=留存用户/前一天新增

3.6.4 如何分析沉默用户？

（登录时间为7天前，且只出现过一次）
按照设备id对日活表分组，登录次数为1，且是在一周前登录。

3.6.5 如何分析本周回流用户？

本周活跃left join本周新增 left join上周活跃，且本周新增id和上周活跃id都为null

3.6.6 如何分析流失用户？

（登录时间为7天前）
按照设备id对日活表分组，且七天内没有登录过。

3.6.7 如何分析最近连续3周活跃用户数？

按照设备id对周活进行分组，统计次数大于3次。

3.6.8 如何分析最近七天内连续三天活跃用户数？

1）查询出最近7天的活跃用户，并对用户活跃日期进行排名
2）计算用户活跃日期及排名之间的差值
3）对同用户及差值分组，统计差值个数
4）将差值相同个数大于等于3的数据取出，然后去重(去的是什么重???)，即为连续3天及以上活跃的用户
7天连续收藏、点赞、购买、加购、付款、浏览、商品点击、退货
1个月连续7天
连续两周：