如果你对数据分析感兴趣，希望学习更多的方法论，希望听听经验分享，
欢迎移步宝藏公众号「小火龙说数据」，无广告、无软文、纯干货，更多精彩原创文章与你分享！
00 原始表介绍
以下面试题背景为电商类消费场景，其中包含「用户表」和「购物消费流水表」两张。
【用户表】ubs_user_profile_di
当日活跃用户，ds+uid为唯一key，每个用户每日仅有一条数据。表结构如下：
【购物消费流水表】ubs_sales_di
当日用户消费详细数据，每一条代表用户购买一次商品，用户每日可购买多次商品。表结构如下：
01 题目1
考核点：单表处理类问题
难度系数：1星
题目：计算20220501日，各年龄活跃用户数，筛选用户数>10000的年龄，并按照用户数降序排列。
输出样式：
考核代码：
select
age
,count(*) as uv
from
ubs_user_profile_di
where
ds = '20220501'
group by
age
having
uv > 10000
order by
uv desc
;02 题目2
考核点：多表关联类问题
难度系数：2星
题目：计算20220501日及往前90日，新用户每日人均消费金额，且保留2位小数(四舍五入)。
输出样式：
考核代码：
select
tmp1.ds as ds
,round(avg(money), 2) as money_per_user
from
--筛选用户
(
select
ds
,uid
from
ubs_user_profile_di
where
ds between date_add('20220501', -90) and '20220501'
and is_new = 1
)tmp1
inner join
--关联用户消费情况
(
select
ds
,uid
,sum(money) as money
from
ubs_sales_di
where
ds between date_add('20220501', -90) and '20220501'
group by
ds
,uid
)tmp2
on
tmp1.ds = tmp2.ds
and tmp1.uid = tmp2.uid
group by
tmp1.ds
;03 题目3
考核点：嵌套类问题
难度系数：2星
题目：计算202205月中，用户消费天数分布，以及人均消费金额。
输出样式：
考核代码：
select
days
,avg(money) as money_per_user
from
(
select
uid
,count(distinct uid) as days
,sum(money) as money
from
ubs_sales_di
where
substr(ds, 1, 6) = '202205'
group by
uid
)tmp
group by
days
;04 题目4
考核点：窗口函数类问题
难度系数：3星
题目：计算20220501日，用户第一次购买的一级品类用户分布量级，按照量级降序排列。
输出样式：
考核代码：
select
category_first
,count(*) as uv
from
(
select
uid
,category_first
,row_number()over(partition by uid order by report_time asc) as rank --分组排序
from
ubs_sales_di
where
ds = '20220501'
)tmp
where
rank = 1
group by
category_first
order by
uv desc
;05 题目5
考核点：窗口函数类问题
难度系数：3星
题目：计算20220401至今，用户第一次购买与第二次购买相差天数，没有第二次购买则不输出。
输出样式：
考核代码：
select
uid
,ds as first_sales_day
,ds_next as second_sales_day
,datediff(to_date(ds_next, 'yyyymmdd'),to_date(ds, 'yyyymmdd')) as date_diff
from
(
select
ds
,uid
,row_number()over(partition by uid order by report_time asc) as rank --用于筛选首次
,lead(ds, 1, 'NULL')over(partition by uid order by report_time asc) as ds_next --用户获取下次购买日期
from
ubs_sales_di
where
ds >= '20220401'
)tmp
where
rank = 1
and ds_next != 'NULL'
;06 题目6
考核点：留存/复购类问题
难度系数：4星
题目：计算20220401-20220430，每日用户量级、次日留存率、3日留存率、7日留存率。
输出样式：
考核代码：
select
ds
,count(*) as uv
,count(if(1_remain>0, 1, null))/count(*) as 1_retention_rate
,count(if(3_remain>0, 1, null))/count(*) as 3_retention_rate
,count(if(7_remain>0, 1, null))/count(*) as 7_retention_rate
from
(
select
user_now.ds as ds
,user_now.uid as uid
,count(if(datediff(to_date(user_after.ds, 'yyyymmdd'),to_date(user_now.ds, 'yyyymmdd')) = 1, 1, null)) as 1_remain --计算用户未来第1日是否能匹配上
,count(if(datediff(to_date(user_after.ds, 'yyyymmdd'),to_date(user_now.ds, 'yyyymmdd')) = 3, 1, null)) as 3_remain --计算用户未来第3日是否能匹配上
,count(if(datediff(to_date(user_after.ds, 'yyyymmdd'),to_date(user_now.ds, 'yyyymmdd')) = 7, 1, null)) as 7_remain --计算用户未来第7日是否能匹配上
from
--当日用户
(
select
ds
,uid
from
ubs_user_profile_di
where
ds between '20220401' and '20220430'
)user_now
left join
--匹配用户未来是否来
(
select
ds
,uid
from
ubs_user_profile_di
where
ds between '20220402' and '20220507' --注意时间
)user_after
on
user_now.uid = user_after.uid
group by
user_now.ds
,user_now.uid
)tmp
group by
ds
;07 题目7
考核点：连续消费/登录类问题
难度系数：5星
题目：计算20220401至今，连续消费3日及以上的用户占比。
输出样式：
考核代码：
select
count(distinct if(ct>=3, uid, null))/count(distinct uid) as 3days_uv_rate
from
--步骤4：计算用户每次连续消费的天数，例如：用户在0401开始连续3日、在0415开始连续2日
(
select
uid
,base_ds
,count(*) as ct
from
--步骤3：计算基准时间，通过基准时间的数量判断连续几日消费，例如：用户在[0401、0402、0403消费]，[rank为1、2、3]，[计算的base_ds为0401往前1日、0402往前2日、0403往前3日，均为0331，则连续3日]
(
select
*
,date_add(ds, -rank) as base_ds
from
--步骤2：按照用户消费日期升序排序
(
select
*
,row_number()over(partition by uid order by ds asc) as rank
from
--步骤1：将用户每日多消费记录去重（单天多条会影响计算）
(
select
ds
,uid
from
ubs_sales_di
where
ds >= '20220401'
group by
ds
,uid
)tmp1
)tmp2
)tmp3
group by
uid
,base_ds
)tmp4
;注意：针对该类问题，除了可以采用以上方式，还可以通过创建udf来实现，后面会针对udf/udtf/udaf做一期分享。
08 注意事项
最后和大家谈谈针对面试中遇到的SQL问题的关注点：
由于是面试，面试官重点关注的是思路，因此在忘记某些函数的情况下，可以将思路输出给面试官，函数是工具，可以随时查询，而思路才是你掌握这个知识的关键；另外，针对某些问题，如果你有多种解答方式，不要吝惜，全部输出给面试官，会是很好的加分项。
以上就是本期的内容分享
如果你也对数据分析感兴趣，那就来关注我吧，更多「原创」文章，与你分享！！
微信公众号：小火龙说数据