数据分析师笔试题 | 真题实战②

历史面试笔试题（点击可查看）：

第一题：用户行为分析

用户行为表event_summary,字段说明如下表；取某一天查看用户资料行为（event_name=profile.index）的20，50，80分位点。以及不同行为频次的用户分布（数据输出格式见表，频次字段可自定义），请以SQL实现。

第一问：取某一天查看用户资料行为（event_name=profile.index）的20，50，80分位点

说明：埋点名称下（事件ID），可以对应多个埋点字段，每个埋点字段又可以有多个枚举值！（这些埋点字段可以映射到公共埋点中）

我们最终限定条件：埋点字段=枚举值！但count(字段)而不是count(枚举值)

select percentile_approx(event_num_map,array(0.2,0.5,0.8),9999)
from event_summary
where Partition_date = ${date}
and event_name=profile.index

第二问：

select t2.行为频次, count(distinct t2.user_id),count(distinct t2.user_id)/count(distinct t1.user_id),sum(count(distinct t2.user_id)) over(partition by  t2.行为频次 )
from (
   select user_id from event_summary
   where Partition_date between $(begin_date) and ${end_date}
)t1
left join(
     select user_id,case when event_num_map between 0 and 5 then '0~5'
            when event_num_map between 6 and 10 then '6~10'
            else '其它'
            end as '行为频次'
            from event_summary 
            where Partition_date between $(begin_date) and ${end_date}
)t2
on t1.user_id=t2.user_id
group by t2.行为频次

第二题：流失用户分析

用户活跃表user_active，该表是以updates方式更新的全量表，字段说明如下表，现需要计算近一个月的日活跃用户量，并按他们的流失天数（即上次活跃距离本次活跃的事件差）给出分布（输出格式见表），请以SQL实现。

根据题干，设计表格

select Partition_date,case when datediff(t2.Partition_date,t1.Partition_date)=1 then count(distinct t1.user_id) end) as '流失1天',
case when datediff(t2.Partition_date,t1.Partition_date)=2 then count(distinct t1.user_id) end) as '流失2天',
case when datediff(t2.Partition_date,t1.Partition_date)=3 then count(distinct t1.user_id) end) as '流失3天',
case when datediff(t2.Partition_date,t1.Partition_date)>=30 then count(distinct t1.user_id) end) as '流失30天以上'，

from (
select user_id,Partition_date
    from usre_active
    where daily_active_status_map=1
)t1
left join (
    select user_id,Partition_date
    from usre_active
    where daily_active_status_map=1
)t2
on t1.user_id=t2.user_id
where t2.user_id is null
group by Partition_date
order by Partition_date desc

补充：

留存与流失可以当作整个公司的阶段性目标！

产品阶段不同，重心也会从拉新转移到留存，对于一个成熟的产品和饱和的市场而言，获取一个新用户的成本可能是留住一个老用户的数倍，流失率的降低也意味着营收的增加，在这种条件下，流失研究的价值是显而易见的。

随之而来的问题：

研究对象是谁：是登录用户、注册用户，还是全部用户的流失率？

流失周期为何：是次日流失率、7日流失率还是月流失率？

如何定义流失：1个月没有访问的用户？2个月没有下单/消费的用户？还是3个月没有登录的用户？

周期划分对用户流失有决定性影响！

流失周期过短，流失预测的准确率低，因为定义为流失的用户中有大量实际留存的用户，只是其使用间隔长而已（比如以1周没登录就算流失，但实际上很多留存用户2~3周才登录一次，也被划分成流失用户）；同时周期过短，定义为留存的用户实际上后来也会流失。

第三题：ABtest

PM计划对用户进行push召回，目标人群为流失3~30天用户，同时有过热保护机制（7天内不重复发），现需要对该策略进行小流量测试。请设计abtest方案，需要解释如何设置分组，如何评估活跃提升的收益。

简要介绍：为了提升用户利用率，可以分层提取用户，UI 层、推荐算法层、广告算法层，或者开屏、首页、购物车、结算页等。

比如将首页用户，分成两拨，一波不进行召回，一波进行召回，统计两组实验的活跃提升率。

前提：要确定显著性指标，可以是页面转化率（conversation rate），也可以是活跃提升率，抑或是收益

统计A，B方案的活跃提升率

A方案：不push召回，自然状态的活跃率（H0）

B方法：push召回，带来的活跃率（H1）

然后确定样本量，假设检验，测试显著，拒绝H0

这一题，写的不好，没经验，请各位看官指正

第四题：如果**dau突然下降，你会如何分析？

本文由求知鸟_舒成整理，问题答案仅做参考

后台回复「进群」，加入读者交流群~

点击「积分」，了解积分规则~