【考试】二阶段2201班考试答案(做错一概不负责)

第一部分：SQL查询

（1）数据库中有如下所示的表1和表2，其中表2包含了2021年一季度的用户充值数据

① 查询在2021年一季度充值最多的用户姓名。

  select  t2.user_name
    from  tb_recharge t1 inner join tb_user t2
      on  t1.user_id = t2.user_id
group by  t1.user_id
order by  sum(pay_amount) desc
   limit  1;

② 查询在2021年一季度没有充值的用户姓名。

select  user_name
  from  tb_user
 where  user_id not in (select  user_id 
                          from  tb_recharge);

③ 查看在2021年一季度连续三个月有充值行为的用户姓名。

  select  t2.user_name
    from  tb_recharge t1 inner join tb_user t2
      on  t1.user_id = t2.user_id
group by  t1.user_id
  having  count(distinct t1.pay_month) = 3;

④ 查询每个用户在2021年一月、二月、三月的充值金额，如下表所示。

  select  t1.user_name as '姓名',
          sum(case t2.pay_month when '一月' then t2.pay_amount else 0 end) as '一月',
          sum(case t2.pay_month when '二月' then t2.pay_amount else 0 end) as '二月',
          sum(case t2.pay_month when '三月' then t2.pay_amount else 0 end) as '三月'
    from  tb_user t1 left join tb_recharge t2
      on  t1.user_id = t2.user_id
group by  t1.user_id;

（2）数据库中有如下所示的用户登录日志表，其中，log_id是自动编号，user_id是用户编号，login_date是用户的登陆日期(date类型)，login_time是用户的登录时间(time类型)，用户每登陆一次，该表就有一条对应的记录，请完成下面的查询。

① 查询单日登陆次数超过3次的用户。

  select  user_id
    from  tb_login_log
group by  user_id, login_date
  having  count(*) >= 3;

② 查询2021年12月每一天的活跃用户数。

  select  login_date as '日期',
		  count(distinct user_id) as '活跃用户数'
    from  tb_login_log
   where  login_date between '2021-12-01' and '2021-12-31'
group by  login_date;

③ 查询2021年12月连续7天登录的用户数。

with temp as (
	select  distinct user_id,
            login_date
      from  tb_login_log
	 where  login_date between '2021-5-01' and '2021-12-31'
)

select  count(distinct user_id) as '连续7天登陆用户数'
  from  (  select  user_id
             from  (select  user_id,
		                    day(login_date) - row_number() over(partition by user_id order by login_date)as diff
                      from  temp) t1
         group by  user_id, diff
           having  count(*) >= 7) t2;

④ 查询2021年12月工作日（周一到周五）的每个时段的活跃用户数，时段如下所示：

• 通勤时段：7：00-9：00和18：00-20：00

• 午休时段：11：00-13：00

• 临睡时段：22：00-1：00

  select  weekday(login_date) as '工作日',
          count(distinct case when login_time between '7:00' and '9:00' or login_time between '18:00' and '20:00' then user_id else null end) as '通勤时段',
          count(distinct case when login_time between '11:00' and '13:00' 
                              then user_id else null end) as '午休时段',
          count(distinct case when login_time >= '22:00' or login_time <= '1:00' 
                              then user_id else null end) as '临睡时段'
    from  tb_login_log
   where  login_date between '2021-5-01' and '2021-12-31'
     and  weekday(login_date) between 0 and 4
group by  weekday(login_date);

第二部分：数据思维

（1）假设成都有2000万常住人口，请估算成都有多少理发师？

需求方：

① 假设一个人平均2个月去一次理发店。

② 全成都2000万人口平均每天理发次数为：$20000000÷60=\frac{1000000}{3}$

供应方：

③ 假设一个理发师每天工作4小时，即240分钟，一个客户服务15分钟。

④ 理发师人数 = $\frac{1000000}{3}÷(240÷15)\approx 20833$（人）

（2）说一下你了解的数据异常排查的方法。

（1）确定异常值

① 数值判定法

$IQR=Q_3-Q_1$

$data<Q_1-1.5\times IQR$ 或 $data>Q_3+1.5\times IQR$

② Z-score判定法

$z=\frac{X-\mu }{\sigma }$

$|z|>3$

（2）确定产生异常值的原因

① 查看数据的真实性：各种人工失误造成数据的不真实。

② 查看数据的波动性：一次性波动（如双十一）、周期性波动（如羽绒服销量和季节强相关）。

③ 查看外部原因的影响：如疫情、就业情况等。

④ 指标拆解。

（3）购买一注双色球（红色球33选6，蓝色球16选1，不考虑顺序）彩票，中奖的概率是多少？

$1-\frac{C_{27}^6\times C_{15}^1}{C_{33}^6\times C_{16}^1}\approx 74$

（4）三角形的三个顶点上各有一只蚂蚁（大小忽略不计），现在他们同时沿着三角形的边以相同的速度爬向下一个顶线，目标随机，三只蚂蚁不会碰面的概率是多少？

$2÷2^3=\frac{1}{4}$

（5）请阐述A/B测试的流程以及可能用到的统计学方法。

① 内部评估：ICE模型

I：Impact：预期影响：能带来明显的指标提升最好

C：Confidence：成功概率：有以往数据支持或源于其他竞品的实践案例。

E：Ease：实现的难易程度：各项投入越低越好。

② 考核标准：通过哪个或哪些指标评估AB测试的效果。

③ 用户分组：实验组和对照组。

④ A/A测试：证明实验组和对照组在同一版本下没有显著差异。

⑤ A/B测试：实验组和对照组使用不同版本的产品。

⑥ 解读指标变化：方差分析。

⑦ 逐步开放新版本给更多用户：持续的数据监测。

⑧ 数据复盘：分析报告。

方差分析：

① 求出组间平方和$S{S}_b$ 和 组内平方和$S{S}_w$

② 分别除以组间自由度$d{f}_b$和组内自由度$d{f}_w$得到$M{S}_b$ 和 $M{S}_w$

③ $F=\frac{M{S}_b}{M{S}_w}$

④ 根据置信区间和两个自由度查表得到$F_{crit}$，如果$F>F_{crit}$则数据有显著性差异。