题目：   请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出   结果按用户等级降序、占比降序排序   除CASE WHEN条件表达式外，这道题目非常能考验学习者对“表联结”和“窗口函数”的基础掌握水平，我认为是算是中上级水平了  也可能是我基础还不够扎实所产生的幻觉，lol   此题有两个解题方法，一个是比较常规的、思路比较结构化，但是比较容冗长的，主要使用的是表联结；另外一个是使用窗口函数的、更加简洁明了的方法  方法 1： 步骤 1：对现有的答题记录，去除未完成的纪录后，再分别打上等级标签  对应不同的分数，有不同的等级标签，这里需要用...

题目： 请找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃 （答题或作答试卷） 在2021年9月的用户信息  个人抛出的问题： 题目不难，不过本人有个问题先抛出，对于找出“以『牛客』开头『号』结尾”的用户，我用的是LIKE，想知道是否有同学使用REGEXP来找出？欢迎指教！  解题思路  我的解题思路是使用表联结，应该不是最直接明了的，看到有同学@logilong 使用了子查询（我这里也先把人家的答案贴上来），我觉得使用子查询更加便于阅读，可以先参考这个答案：  select uid, nick_name, achievement from user_info ...

题目： 请计算2021年10月商城里所有新用户的首***均交易金额（客单价）和平均获客成本（保留一位小数） 注：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额}  解题思路 步骤 1：先找出10月份的新用户，再找出其对应的第一笔订单  21年10月份的新用户，可以理解为：首单发生在2021年10月的用户。查询语句为：  SELECT uid, MIN(event_time) AS first_dt /* 最小的订单日期即为首单日期 */ FROM tb_order_overall WHERE status = 1 /* 确保是已完成的订单 */ G...

题目： 1）请统计国庆头3天里，每天的近7日 日均订单完成量和日均订单取消量，保留2位小数 2）结果按日期升序排序 考察知识点： 1）窗口函数中的 移动平均计算  题解：有“拆解版”，和“一步到位版” 【拆解版】 步骤1：找出每天的订单完成量和订单取消量 SELECT DATE(order_time) AS dt, /* 转化成日期 */ COUNT(mileage) AS finish, /* 完成量 */ COUNT(CASE WHEN mileage IS NULL THEN 1 ELSE NULL END) AS cancel /* 取消量：当mileage为空时，则代表该订单是未发生...

题目： 请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序  坑点 1）总活跃月份数： 全部月份都算，无论是2021年8月还是2020年8月，都算 2）试卷活跃天数：只要试卷的start_time存在，即视为用户在该天活跃 3）答题活跃天数：只要题目的submit_time存在，即视为用户在该天活跃 4）2021年活跃天数：   这是最大的难点，它不是“试卷活跃天数”与”答题活跃天数“的简单相加！！当在同一天（e.g. 2021年8月1日，同一个用户在该天即做过试卷（exam）也做过题目（pr...

这道题也很有意义，对于多表连接的运转逻辑和很有帮助

题目： 找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序  难点：开窗函数、CASE WHEN的行列转换  第二快和第二慢：这里就需要用到开窗函数来进行排序（我使用的是DENSE_RANK()，原因下面会仔细阐述） 要将每个exam_id的第二快和第二慢相减，必须使两者处于同一行，这里就考察CASE WHEN的高频用法：行列转换 使用TIMESTAMPDIFF的时候，单位参数需要使用SECOND才是最准确的​​​ 开窗函数的用法：NTH_VALUE 函数（ps：本次解题没用） 在看别人的解答时，发现MySQL的开窗函数中还有提供NTH_VALUE(expression...

题目： 请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序  条件 1）2021年 2）至少有两天作答：必须是有两天，即便是同一天做了两张试卷，也视为只有一天 3）连续两次作答间的时间差最大 4）历史规律：全周期内（非两次连续作答周期内），平均每天做多少张试卷 5）days_window天里平均会做多少套试卷: 历史规律得出的平均数 * 最大时间差 6）按最大时间窗和平均做答试卷套数倒序排序 题目示例：用户1006分别在20210...

题目：请统计零食类商品中复购率top3高的商品，结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序  指标：某商品复购率 = 近90天内，购买它至少两次的人数 ÷ 购买它的总人数；其中，近90天指包含最大日期（记为当天）在内的近90天 注意，这里有个坑点是，“近90天”是指距离tb_order_overall表中最大日期的近90天（包含最大日期当天），而非每一件商品各自的近90天；题目没讲清楚，使得部分同学解题难度加深了，即使结果其实是对的但也过不了用例  解题思路 步骤一：找出零食类商品中，在近90天内，每件商品被每个用户购买的次数，从而找出有复购的用户 这一步骤的难点在于，定位近9...

题目：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出  条件： 1）2021年 2）每次都能有85分 OR 至少有一次用小于等于限定时间，且分数大于80 完成高难度试卷 3）按照用户ID升序输出  解题思路 步骤一：找出，2021年中，每次试卷都有85分的人 一开始想着用各种复杂的办法解决，比如说去判别COUNT(uid) 是否与 score>=85 的记录数相等，来选取符合条件的uid，语句如下（不推荐使用，无必要）： # 方法一 SELECT uid, (CASE WHEN COUNT(...

题目需求：统计2021年11月每天新用户的次日留存率（保留2位小数）  条件： 1）只需要2021年11月的数据行； 2）次日留存率为【当天新增的用户数】中第二天又活跃了的用户数占比 —— 第一个坑点，只限于当天新增的用户，若用户在11-01、11-02、11-03、11-04都出现过，则只考虑11-01的留存率； 3）in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过 —— 这是本题最大的坑点！！即，只要用户在第一天登陆，一直到第二天才退出的话，也算做该用户在次日留存下来了；  解题思路 步骤1：确认每个用户的第一次登陆的日期，因为留存率只针对该日期来计算...

题目考核知识点：开窗函数中“移动平均”的变体用法（MAX、SUM）  解题思路： 第一步：先计算出每个类别，在每天的总点赞数、总转发数。这一步需要注意一点，video_id 2001和2002属于“旅游”类别，因此不要GROUP BY video_id，而是GROUP BY tag SELECT i.tag, DATE(l.start_time) AS dt, SUM(l.if_like) AS like_per_dt, SUM(l.if_retweet) AS retweet_per_dt FROM tb_user_video_log AS l INNER JOIN tb_video_inf...

题目：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 要求条件： 1）仅限2021年 2）结果按创作者ID、总粉丝量升序排序 指标： 1）涨粉率 = (加粉量 - 掉粉量) / 播放量 2）字段“if_follow” —— 1代表点击关注，0代表未点击关注，2为取消关注 解题思路 第一步：确认2021年中，每个作者、在每一个月中的实际涨粉量 & 播放量，才能求出每个作者在每个月的转粉率 这一步很简单，只需要联结两个表即可，对作者、月份进行GROUP BY。其中唯一需要注意的是，因为字段if_follow的值只有0、1、2，而非可直接用于计算的-1（掉粉）、0（无变化）、1（...