图解面试题：如何分析红包领取情况？

【题目】

“用户活跃表”记录了用户的登录信息，包括用户标识、用户登录日期，以及是否是新用户（如果是新注册的用户值为1；如果是老用户，值为0）。

“领取红包表”里记录了用户领取红包的信息，包括抢红包日期、抢红包时间、用户ID、领取红包金额。

现在业务部门需要分析出以下问题：

1.计算2019年6月1日至今，每日DAU（活跃用户是指有登陆的用户）

2.分析每天领取红包的用户数、人均领取金额、人均领取次数，要考虑用户属性及领取红包未登录情况。

3.分析每个月按领红包取天数为1、2、3……30、31天区分，计算取每个月领取红包的用户数，人均领取金额，人均领取次数。

4.分析每个月领过红包用户和未领红包用户的数量

【分析思路】

1.计算2019年6月1日至今，每日DAU（活跃用户是指有登陆的用户）

每日DAU（有登陆的用户数）用到的表是“用户活跃表”。当出现“每天”要想到《猴子 从零学会sql》里讲过的分组汇总来解决这样的问题。

用“日期”来分组（group by），用count(用户ID)来汇总登录用户。

sql代码如下

select 登录日期,count(用户ID) as 活跃用户量DAUfrom 活跃用户表where 登录日期>20190601group by 登录日期;

查询结果如下

2.分析每天领取红包的用户数、人均领取金额、人均领取次数，要考虑用户属性及领取红包未登录情况。注意：保留两位小数。

（1）“每天领取红包的用户”包括新用户、老用户、领取红包但未登录的用户。新、老用户数用到的表是“用户活跃”表，领取红包但未登录的用户数要用到两个表：“用户活跃”表和“领取红包”表。

（2）“人均领取金额”、“人均领取次数”用到的表是“领取红包”表。

这里涉及到两个表“用户活跃”表和“领取红包”表，所以遇到多表查询的情况，要想到多表联结。下图是两表联结的条件（通过用户ID和日期联结）。

（3）使用哪种联结呢？

因为要查询的是领取红包的用户，所以要保留“领取红包”表（上图右表）中的全部数据，因此使用右联结。

两表联结的sql如下：

select n.抢红包日期,n.用户ID,n.金额,m.新用户from 用户活跃表 as mright join 领取红包表 as non m.登录日期=n.抢红包日期 and m.用户ID=n.用户ID;

【解题步骤】

（1）将“每天领取红包的用户”即新用户、老用户、领取红包但未登录的用户进行区分。

也就是“用户或活跃”表和“领取红包”表右联结，如果"新用户“=1 则是新用户，"新用户“=0则是老用户，如果"新用户“= null表示两表没有匹配的值，说明该用户未登录。

涉及到多条件判断的问题，要想到用case语句来实现：

case when 新用户=1 then 新用户     when 新用户=0 then 老用户     else 未登录用户end

把两表联结的sql代入上面sql，就得到了：

select n.抢红包日期,n.用户ID,n.金额,(case when m.新用户=1 then 新用户      when m.新用户=0 then 老用户      else 未登录用户end) as 新老用户标识from 用户活跃表 as mright join 领取红包表 as non m.登录日期=n.抢红包日期 and ,m.用户ID=n.用户ID;

查询结果如下：

（2）把上一步的查询结果记为临时表a。从a表中，用分组汇总，求出每类用户的个数，人均领取金额，人均领取次数。

人均领取金额=总金额/用户数=sum(金额)/count(distinct 用户ID)

人均领取次数=领取次数（也就是有多少行数据）

=count(*)/count(distinct 用户ID) 

（3）将上述两个步骤的sql合并到一起，就是最终sql

select a.抢红包日期,count(distinct case when a.新老用户标识='新用户' then 用户ID else null end) as 新用户数,count(distinct case when a.新老用户标识='老用户' then 用户ID else null end) as 老用户数,count(distinct case when a.新老用户标识='未登录用户' then 用户ID else null end) as 未登录用户,sum(a.金额)/count(distinct a.用户ID) as 人均领取金额,count(*)/count(distinct a.用户ID) as 人均领取次数from (select n.抢红包日期,n.用户ID,n.金额,(case when m.新用户=1 then 新用户      when m.新用户=0 then 老用户      else 未登录用户end) as 新老用户标识from 用户活跃表 as mright join 领取红包表 as non m.登录日期=n.抢红包日期 and ,m.用户ID=n.用户ID) as agroup by a.抢红包日期;

结果如下图

3.分析每个月按领红包取天数为1、2、3……30、31天区分，计算取每个月领取红包的用户数，人均领取金额，人均领取次数。注意：保留两位小数。

【分析思路】

领取红包用到的表是“领取红包”表。出现“每个月”这样的字眼，和就要想到用分组汇总来解决此类问题，按“月份”分组。

使用month(日期)获取月份。

人均领取金额=总金额/去重的用户数=sum(金额)/count(distinct 用户ID)

人均领取次数=领取次数（也就是有多少行数据）

=count(*)/count(distinct 用户ID) 

select month(抢红包日期) as 月份,count(distinct 抢红包日期) as 领取天数,count(distinct 用户ID) as 每个月领取红包的用户数,sum(金额)/count(distinct 用户ID), as 人均领取金额,count(*)/count(distinct 用户ID) as 人均领取次数from 领取红包表group by month(抢红包日期);

查询结果如下：

4. 分析每个月领过红包用户和未领红包用户的数量

【分析思路】

“领过红包用户”和“未领红包用户”什么意思？

将“用户登录”表和“领取红包”表进行左联结。

select m.登录日期, m.用户ID,n.用户ID as 红包用户IDfrom 用户活跃表 as mleft join 领取红包表 as non a.登录日期 = b.抢红包日期 and a.用户ID = b.用户ID;

如果“领取红包”表中的用户为非空，那么该用户是“领过红包用户”（也就是在“领取红包”表中匹配到了数据）；如果“领取红包”表中的用户为空的，那么该用户是“未领取红包用户”（也就是在“领取红包”表没有匹配到数据）。用case语句来判断。

(case when 用户ID  is not null then 领过红包用户     else  未领取红包用户 end) as 是否红包用户

把case语句代入上面左联结的sql就是：

select m.登录日期, m.用户ID,n.用户ID as 红包用户ID,(case when 用户ID  is not null then 领过红包用户     else  未领取红包用户 end) as 是否红包用户from 用户活跃表 as mleft join 领取红包表 as non a.登录日期 = b.抢红包日期 and a.用户ID = b.用户ID;

把上面查询结果记为临时表a，题目要分析的是“每个月领过红包用户”和“每个月未领红包用户”，用户分组汇总来解决此类问题。按月份分组（group by）用sum来统计数量。

select month(登陆日期),sum(case when a.是否红包用户='领过红包用户' then 1           else 0      end) '红包用户数',sum(case when a.是否红包用户='未领取红包用户' then 1            else 0      end)  '非红包用户数’from agroup by mount(登陆日期);

将临时表a的sql代入上面sql语句，查询结果如下图

【本题考点】

1.多表查询的理解和灵活应用，记住下图可以解决99%的多表查询问题。

2.有“每个”出现的时候，要想到用分组汇总来解决。

3.条件判断的问题，用case语句来解决，例如本文的面试题“新用户“、”老用户“。

4.按条件统计数量的时候，要结合case语句和sum来统计数，例如之前课程里讲过的下面案例

​