sql留存用户率练习题

概述

手机中的相机是深受大家喜爱的应用之一，现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据：

某日活跃用户（用户id）在后续的一周内的留存情况（计算次日留存用户数，3日留存用户数，7日留存用户数）

指标定义

• 某日活跃用户数，某日活跃的去重用户数。

• N日活跃用户数，某日活跃的用户数在之后的第N日活跃用户数。

• N日活跃留存率，N日留存用户数/某日活跃用户数

• 例：登陆时间（20180501日）去重用户数10000，这批用户在20180503日仍有7000人活跃，则3日活跃留存率为7000/10000=70%

  数据集app_user_info：

字段名	字段类型	备注
uid	string	用户唯一标识
app_name	string	手机app名称，如“相机”，“微信”等
duration	bigint	启动时长（天）
times	bigint	启动次数（天）
dayno	string	表分区字段，如2018-05-1

所获得的结果如下：

日期	活跃用户数	次日留存用户数	三日留存用户数	七日留存用户数	次日留存率	三日留存率	七日留存率
20180501
20180502
20180503
…
20180508
20180509
20180510

---

要求：

1、活跃用户数为整数

2、留存率表示为百分比，保留2位小数

3、仅一条SQL（或者 hive SQL)完成。

4、仅需写出实现的查询代码即可。

思路

• 首先搞清楚所需字段：日期（dayno)和用户数（uid）

• 由于一天用户可以多次打开一个app，一天会形成多条记录，也就是一天用户ID会出现多次，并且这一天的日期字段会出现多次，所以对需要对uid，dayno进行分组，相当于去重处理

• 只需筛选相机的数据，并且根据要求的结果呈现形式需要对日期进行格式化处理

解答

SELECT a.day1 AS '日期',COUNT(DISTINCT a.uid) AS '活跃用户数',
SUM(IF(b.day1-a.day1=1,1,0)) AS '次日留存用户数',
SUM(IF(b.day1-a.day1=2,1,0)) AS '三日留存用户数',
SUM(IF(b.day1-a.day1=6,1,0)) AS '七日留存用户数',
CONCAT(ROUND(SUM(IF(b.day1-a.day1=1,1,0))/COUNT(DISTINCT a.uid)*100,2),'%') AS '次日留存率',
CONCAT(ROUND(SUM(IF(b.day1-a.day1=2,1,0))/COUNT(DISTINCT a.uid)*100,2),'%') AS '次日留存率',
CONCAT(ROUND(SUM(IF(b.day1-a.day1=6,1,0))/COUNT(DISTINCT a.uid)*100,2),'%') AS '次日留存率'
FROM
(SELECT uid,DATE_FORMAT(dayno,'%Y%m%d') AS day1
FROM app_user_info WHERE app_name = '相机' GROUP BY uid,day1) a
LEFT JOIN 
(SELECT uid,DATE_FORMAT(dayno,'%Y%m%d') AS day1
FROM app_user_info WHERE app_name = '相机' GROUP BY uid,day1) b
ON a.uid = b.uid WHERE a.day1 <=b.day1 
GROUP BY a.day1