龙空技术网

7道数据分析面试题,涵盖80%常考知识点「SQL篇」

小火龙说数据 133

前言:

现时兄弟们对“sql怎么计算年龄”大致比较珍视,我们都想要分析一些“sql怎么计算年龄”的相关文章。那么小编同时在网上搜集了一些对于“sql怎么计算年龄””的相关文章,希望姐妹们能喜欢,咱们快快来了解一下吧!

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

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

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

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

以上就是本期的内容分享

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

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

标签: #sql怎么计算年龄