如果你对数据分析感兴趣，希望学习更多的方法论，希望听听经验分享，

欢迎移步宝藏公众号「小火龙说数据」，无广告、无软文、纯干货，更多精彩原创文章与你分享！

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问题的关注点：

由于是面试，面试官重点关注的是思路，因此在忘记某些函数的情况下，可以将思路输出给面试官，函数是工具，可以随时查询，而思路才是你掌握这个知识的关键；另外，针对某些问题，如果你有多种解答方式，不要吝惜，全部输出给面试官，会是很好的加分项。

以上就是本期的内容分享

如果你也对数据分析感兴趣，那就来关注我吧，更多「原创」文章，与你分享！！

微信公众号：小火龙说数据