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

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

00 原始表介绍

以下面试题背景为电商类消费场景，其中包含「用户表」和「购物消费流水表」两张。

【用户表】ubs_user_profile_di

当日活跃用户，ds+uid为唯一key，每个用户每日仅有一条数据。表结构如下：

【购物消费流水表】ubs_sales_di

当日用户消费详细数据，每一条代表用户购买一次商品，用户每日可购买多次商品。表结构如下：

01 题目1

考核点：单表处理类问题

难度系数：1星

题目：计算20220501日，各年龄活跃用户数，筛选用户数>10000的年龄，并按照用户数降序排列。

输出样式：

考核代码：

select    age    ,count(*) as uvfrom    ubs_user_profile_diwhere    ds = '20220501'group by    agehaving    uv > 10000order by    uv desc;

02 题目2

考核点：多表关联类问题

难度系数：2星

题目：计算20220501日及往前90日，新用户每日人均消费金额，且保留2位小数(四舍五入)。

输出样式：

考核代码：

select    tmp1.ds as ds    ,round(avg(money), 2) as money_per_userfrom    --筛选用户    (    select        ds        ,uid    from        ubs_user_profile_di    where        ds between date_add('20220501', -90) and '20220501'        and is_new = 1    )tmp1inner 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    )tmp2on    tmp1.ds = tmp2.ds    and tmp1.uid = tmp2.uidgroup by    tmp1.ds;

03 题目3

考核点：嵌套类问题

难度系数：2星

题目：计算202205月中，用户消费天数分布，以及人均消费金额。

输出样式：

考核代码：

select    days    ,avg(money) as money_per_userfrom    (    select        uid        ,count(distinct uid) as days        ,sum(money) as money    from        ubs_sales_di    where        substr(ds, 1, 6) = '202205'    group by        uid    )tmpgroup by    days;

04 题目4

考核点：窗口函数类问题

难度系数：3星

题目：计算20220501日，用户第一次购买的一级品类用户分布量级，按照量级降序排列。

输出样式：

考核代码：

select    category_first    ,count(*) as uvfrom    (    select        uid        ,category_first        ,row_number()over(partition by uid order by report_time asc) as rank --分组排序    from        ubs_sales_di    where        ds = '20220501'    )tmpwhere    rank = 1group by    category_firstorder 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_difffrom    (    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'    )tmpwhere    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_ratefrom    (    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    )tmpgroup by    ds;

07 题目7

考核点：连续消费/登录类问题

难度系数：5星

题目：计算20220401至今，连续消费3日及以上的用户占比。

输出样式：

考核代码：

select    count(distinct if(ct>=3, uid, null))/count(distinct uid) as 3days_uv_ratefrom    --步骤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问题的关注点：

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

以上就是本期的内容分享

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

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