Hive面试题——次日，3日，7日，30日，90日留存计算

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

在日常工作中，用户留存分析是一个非常常见的业务场景。尤其是像互联网这样的重流量行业，用户留存就显得更加重要了。用户留存，顾名思义就是公司获取的新用户，一段时间内的活跃情况。在数据分析或者数据仓库的面试中，多个周期的留存率（3日、7日、14日、30日、90日）的宽表生成也基本是必问题目。

题目的内容基本大同小异，大概意思如下：

已知有一张用户登录日志表，user_login_log，表结构如下：

user_id	event_time
1	2022-01-01 12:10:00
2	2022-01-11 22:10:00
3	2022-01-21 21:20:01
......	........

请计算次日、3日、7日、14日、30日、90日留存率，并放在同一张报表中。

首先理一下解题思路：

1、首先按照日期和用户维度去重，作为子查询

2、然后用去重后的子查询与原数据表做子关联，关联条件为：user_id相同，时间做差

3、再分别取不同的差，进行去重计数，之后计算对应的留存率即可

SQL代码：

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_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.user_id AS user_id_d1,
			c.user_id AS user_id_d3,
			d.user_id AS user_id_d7,
			f.user_id AS user_id_d30,
			g.user_id AS user_id_d90
		FROM
			(
				SELECT DISTINCT
					Date(event_time) AS log_day,
					user_id AS user_id_d0
				FROM
    					user_login_log
				ORDER BY
					log_day
			) a
		LEFT JOIN user_login_log b ON DATEDIFF(DATE(b.event_time),a.log_day) = 1
		AND a.user_id_d0 = b.user_id
		LEFT JOIN user_login_log c ON DATEDIFF(DATE(c.event_time),a.log_day) = 2
		AND a.user_id_d0 = c.user_id
		LEFT JOIN user_login_log d ON DATEDIFF(DATE(d.event_time),a.log_day) = 6
		AND a.user_id_d0 = d.user_id
		LEFT JOIN user_login_log f ON DATEDIFF(DATE(f.event_time),a.log_day) = 29
		AND a.user_id_d0 = f.user_id
		LEFT JOIN user_login_log g ON DATEDIFF(DATE(g.event_time),a.log_day) = 89
		AND a.user_id_d0 = g.user_id
	) AS temp
GROUP BY
log_day
;