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

【题目】

“用户活跃表” 记录了用户的登录信息，包括用户标识、用户登录日期，以及是否是新用户（如果是新注册的用户值为 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 活跃用户量DAU
from 活跃用户表
where 登录日期>20190601
group by 登录日期;

查询结果如下

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

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

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

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

（3）使用哪种联结呢？

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

两表联结的 sql 如下：

select n.抢红包日期,n.用户ID,n.金额,m.新用户
from 用户活跃表 as m
right join 领取红包表 as n
on 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 m
right join 领取红包表 as n
on 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( case when a.新老用户标识='老用户' then 用户ID else null end) as 老用户数,
count( 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 m
right join 领取红包表 as n
on m.登录日期=n.抢红包日期 and ,m.用户ID=n.用户ID
) as a
group 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 红包用户ID
from 用户活跃表 as m
left join 领取红包表 as n
on 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 m
left join 领取红包表 as n
on 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 a
group by mount(登陆日期);

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

【本题考点】

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

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

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

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

推荐：如何从零学会 sql？

Previous图解面试题：如何分析用户满意度？Next图解面试题：如何分析中位数？

Last updated

Was this helpful?