SQL152 注册当天就完成了试卷的名单第三页1、首先找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人的uid2、然后找出该uid用户的对应信息和最大成绩，并使用limit过滤前6行且只取3行，来实现取第三页的数据with t1 as (select    exam_record.uid uidfrom     user_info right join exam_record on user_info.uid=exam_record.uid    left join examination_info on examination_info.exam_id=exam_record.exam_idwhere     job ='算法' and date(register_time)=date(start_time)     and tag='算法' and submit_time is not null)select     exam_record.uid,level,register_time,max(score) max_scorefrom     user_info right join exam_record on user_info.uid=exam_record.uid    left join examination_info on examination_info.exam_id=exam_record.exam_idwhere    exam_record.uid in (select uid from t1)group by     uid,level,register_timeorder by     max_score desclimit 3 offset 6

SQL140 未完成率较高的50%用户近三个月答卷情况1、首先先统计出SQL试卷所有用户的未答数，答题总数，未完成率2、通过给增加排名来取出50%的用户。使其按照未完成率降序，排名小于(最大名次+1)/2(+1是为了向上取整)的就是前50%的用户。3、使用不跳过排名函数DENSE_RANK()来取出最近三个月的答卷情况4、将所有的条件结合到一起，排好序，输出所需即可with t1 as(select exam_record.uid uid    ,sum(if(submit_time is null,1,0)) incomplete_cnt,count(exam_record.uid) total_cnt    ,sum(if(submit_time is null,1,0))/count(exam_record.uid) incomplete_rate    ,row_number() over(order by sum(if(submit_time is null,1,0))/count(exam_record.uid) desc) ranking  -- 统计SQL试卷上用户的未完成数、作答总数、未完成率，用户idfrom     exam_record left join examination_info     on examination_info.exam_id=exam_record.exam_idwhere tag='SQL' group by uid),t2 as(select uid from t1  where ((select max(ranking) from t1 t)+1)/2>=ranking -- 根据未完成率选出50%的用户),t3 as (SELECT uid, submit_time, DATE_FORMAT(start_time, "%Y%m") as start_month,    DENSE_RANK() over(        ORDER BY DATE_FORMAT(start_time, "%Y%m") DESC        ) as start_month_rank -- 按作答月份降序编号，选出近三个月的月份FROM exam_record)SELECT uid, start_month, COUNT(1) as exam_cnt, COUNT(submit_time) as complete_cntFROM  t3WHERE start_month_rank AND uid in (select uid from t2) AND uid IN (SELECT uid FROM user_info WHERE `level`>=6)GROUP BY uid, start_monthORDER BY uid, start_month;

SQL141 试卷完成数同比2020年的增长率及排名变化1、首先计算出上半年每个tag的作答记录以及排名2、因为只能返回具有同比的tag记录，故可以用count()>=2筛选出tag，然后在聚合函数中使用case实现将行转为列，将20年的作答数和排名和21年的作答数和排名合并成一列3、最后进行对应的计算即可with t1 as(select tag,year(start_time) start_year,count(exam_record.exam_id) exam_cnt    ,rank() over(partition by year(start_time) order by year(start_time) asc,count(exam_record.exam_id) desc) exam_cnt_rankfrom    exam_record left join examination_info    on examination_info.exam_id=exam_record.exam_idwhere submit_time is not null and month(submit_time)group by tag,year(start_time)),t2 as(select tag,sum(case when start_year=2020 then exam_cnt end ) exam_cnt_20    ,sum(case when start_year=2021 then exam_cnt end ) exam_cnt_21    ,sum(case when start_year=2020 then exam_cnt_rank end ) exam_cnt_rank_20    ,sum(case when start_year=2021 then exam_cnt_rank end ) exam_cnt_rank_21from t1where tag in (select tag from t1 group by tag having count(tag)>=2)group by tag)select tag,exam_cnt_20,exam_cnt_21    ,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') growth_rate    ,exam_cnt_rank_20,exam_cnt_rank_21    ,exam_cnt_rank_21-exam_cnt_rank_20 rank_deltafrom t2order by round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1) desc,exam_cnt_rank_21 desc

SQL165 统计活跃间隔对用户分级结果分析题目后可得：# 新晋用户:全表最大时间(今天)-注册时间# 沉睡用户:全表最大时间(今天)-最近登录>=7 and 全表最大时间(今天)-最近登录# 流失用户:全表最大时间(今天)-最近登录>=30# 忠实用户:全表最大时间(今天)-最近登录=7故首先要找出每个用户的注册时间、最近登录时间、全表最大时间(作为今天)，然后根据题意对每个用户进行分级，最后分组求出每个级别的人数并除以总用户数(select count(distinct uid) from tb_user_log)，即可得出每个级别的占比情况with t1 as(select     uid    ,(select max(date(in_time)) from tb_user_log) as max_intime # 全表最大时间(今天)    ,max(date(in_time)) as max_in_time # 每个用户登录的最大时间(最近登录)    ,min(date(in_time)) as min_in_time # 每个用户登录的最小时间(注册时间)from    tb_user_loggroup by     uid),t2 as(select    uid,    case    when datediff(max_intime,min_in_time) when datediff(max_intime,max_in_time)>=7 and datediff(max_intime,max_in_time) when datediff(max_intime,max_in_time)>=30 then '流失用户'    else '忠实用户'end user_grade from     t1group by    uid)select     user_grade    ,round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratiofrom    t2group by    user_gradeorder by    ratio desc

SQL183 某宝店铺动销率与售罄率# 有销售的SKU数量：售出的SKU数量总和（商品期间销售数量）# 在售的SKU数量 ：剩余（总库存 - 售出）库存SKU的数量总和（商品期末库存数量）# GMV ：成交总金额因为一个货物有可能出现多次销售的情况，若直接连接，会出现product_tb产品情况表中的一条数据与sales_tb销售数据表中的多条数据相连，会导致inventory库存变大，使的计算结果变小的情况。故要先对sales_tb销售数据表进行数据预处理，将其中的数据根据货号进行分组计算。select          style_id    ,round(sum(sales_num)*100/(sum(inventory)-sum(sales_num)),2) `pin_rate(%)`    ,round(sum(sales_price)*100/sum(tag_price*inventory),2) `sell-through_rate(%)`from     product_tb left join (        select         item_id        ,sum(sales_num) sales_num        ,sum(sales_price) sales_price        from sales_tb        group by item_id         ) sales_tb on product_tb.item_id=sales_tb.item_idgroup by    style_id

SQL178 工作日各时段叫车量、等待接单时间和调度时间叫车量：因为每一次打车记录都赋予了时段，故count(period)即为某一时段的叫车量等待时间：结束打车时间-打车时间。因为有可能出现打车过程中取消订单的情况，此时order_id为null，使用if()将其筛掉即可。调度时间：即司机过来接你上车所用的时间。start_time-order_time可以通过case来给period字段分配多个值，是否工作日可以用dayofweek判断：1为周日，2为周一以此类推;time()函数将日期格式化为时分秒的类型，使用加减运算则返回秒数，再除以60就可以转换为分钟;with t1 as(select    case when time(event_time)>='07:00:00' and time(event_time) when time(event_time)>='09:00:00' and time(event_time) when time(event_time)>='17:00:00' and time(event_time)    else '休息时间'    end period    ,if(tb_get_car_record.order_id is null,null,timestampdiff(second,event_time,end_time)) wait_time    ,timestampdiff(second,order_time,start_time) as dispatch_timefrom    tb_get_car_record left join tb_get_car_order    on tb_get_car_record.order_id=tb_get_car_order.order_idwhere dayofweek(event_time) >1    and     dayofweek(event_time) )select     period    ,count(period) as get_car_num    ,round(avg(wait_time)/60,1) as avg_wait_time    ,round(avg(dispatch_time)/60,1) as avg_dispatch_timefrom     t1group by    periodorder by    get_car_num asc

1、提取时间# 获取当前日期时间select NOW(); # 2021-04-02 09:25:29# 获取当前日期SELECT CURDATE(); # 2021-04-02# 获取当前时间SELECT CURTIME();  # 09:26:10# 对于时间2021-04-02 09:25:29，分别获取其年、月、日、时、分、秒SELECT EXTRACT(YEAR FROM NOW()); # 2021SELECT EXTRACT(MONTH FROM NOW()); # 4SELECT EXTRACT(DAY FROM NOW()); # 2SELECT EXTRACT(HOUR FROM NOW()); # 9SELECT EXTRACT(MINUTE FROM NOW()); # 25SELECT EXTRACT(SECOND FROM NOW()); # 29# 或者从日期格式字符串中获取SELECT EXTRACT(SECOND FROM '2021-04-02 10:37:14.123456');  # 142、时间的加减运算# 时间减少1小时（前一小时）select date_sub(now(), INTERVAL 1 hour);# 日期增加1天select date_add(now(), INTERVAL 1 day);# 其他间隔INTERVAL 1 YEAR  # 1年INTERVAL 1 MONTH  #一个月INTERVAL 1 DAY  # 1天INTERVAL 1 HOUR  # 1小时INTERVAL 1 MINUTE  # 1分钟INTERVAL 1 SECOND  # 1秒# 计算两个日期的差值datediff(date1,date2)：两个日期相减 date1 - date2，返回天数TIMEDIFF(time1,time2)：两个日期相减 time1 - time2，返回 TIME 差值(秒)TIMESTAMPDIFF(x, 1, time1,time2)：以x为单位，返回time2-time1相差的值，x可以为year、month、day、hour、minute、second(年月日时分秒)3、时间格式化date_format(you_data,'%Y%-m-%d %H:%i:%s')    # 2021-04-02 09:25:29date()    # 返回年月日格式：    2021-04-02time()    # 返回时分秒格式：    09:25:29str_to_date(str,format) #str_to_date("2021年4月2日",'%Y年%m月%d日') =>得到日期格式的：'2021-04-02'4、其他函数dayofweek(you_date);--判断星期几：1为周日,2为周一,...;SELECT YEAR('2017-05-15 10:37:14.123456');-- 获取年份SELECT MONTH('2017-05-15 10:37:14.123456');-- 获取月份SELECT DAY('2017-05-15 10:37:14.123456');-- 获取日SELECT HOUR('2017-05-15 10:37:14.123456');-- 获取时SELECT MINUTE('2017-05-15 10:37:14.123456');-- 获取分SELECT SECOND('2017-05-15 10:37:14.123456');-- 获取秒SELECT MICROSECOND('2017-05-15 10:37:14.123456');-- 获取毫秒SELECT QUARTER('2017-05-15 10:37:14.123456');-- 获取季度SELECT WEEK('2017-05-15 10:37:14.123456');-- 20 (获取周)                        原文链接：https://blog.csdn.net/hu1010037197/article/details/115391335

首先需要对attend_tb表进行预处理，把一天内多次登录的数据只保留一条数据，避免出现重复计算，同时筛选出观看时长大于等于10的数据with t1 as (    select distinct         user_id,course_id    from         attend_tb    where  timestampdiff(minute,in_datetime,out_datetime)>=10)select     behavior_tb.course_id    ,course_name    ,round(count(t1.user_id)*100/sum(if_sign),2) `attend_rate(%)`from    course_tb right join behavior_tb on course_tb.course_id=behavior_tb.course_id    left join t1 on behavior_tb.course_id=t1.course_id and    behavior_tb.user_id=t1.user_idgroup by    behavior_tb.course_id,course_nameorder by    course_id asc

笔试（7-26）：    根据题型来划分有选择题、判断题、简答题、SQL题、职场题，大部分都忘了，只能想起来一点，主要涉及到Hivesql，shell，Linux    选择题(15道左右)：        shell中参数$0代表什么，        shell if中 -ge代表什么，        Linux ls命令哪个参数是以文件修改时间来排序，        sql between 5 and 9返回的结果，        insert into属于什么语句(A.DDL  B.DML  C.DCL  D.DLL)        shell 中for的结束语句是什么            判断题(10道左右)        like Hive 建表语句是否会修改外部表结构        sql sum函数只能对数字列进行求和吗            简答题(五道)：        union与union all的区别，        shell中-eq与==的区别，        Hive中的去重方法都有哪些（至少举例两种）        Hive rank over(),Row_Number()的区别        还是一道是关于sql between ... and ...的使用，具体内容忘了            SQL题(1道5小问)        前四道都是普通sql，几乎都涉及到日期函数；最后一道是使用shell for 与sql结合来解答        职场题        如果你发现该岗位做的业务和你想的不一样，你会怎么办        当你拥有以下的事情，你会如何进行安排(a.下午开会的报告   b.客户的新想法 c.自我学习 d.下周三提交的  e......  )面试(7.30)：        自我介绍，聊项目(sgg数仓)，为什么要搭建数仓，你当时为什么选择做这个数仓，搭建数仓大概用了多久，里面的数据哪里来，存在哪里，搭建数仓的流程，数仓每层干什么的，你知道拉链表吗        sql中where 和having的区别        Hive 中insert into 和insert owerwrite的区别        shell 命令中参数$0,$1....$9是什么意思        shell中.RC后缀的文件是什么文件        shell if中哪个是判断是否等于，大于等于是那个        你了解窗口函数吗，讲一下        假设数据库中的数据量很大，如何进行优化          你觉得笔试中的SQL题难度怎么样        如果你发现该岗位做的业务和你想的不一样，你会怎么办        如果你到岗之后，学校要求你回去，你会怎么办