/*题意：找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人 求解：1.求职方向与注册时间的uid可以从userinfo获取 */ select u.uid,u.level,u.register_time,max(e.score) as max_score from (select uid,level, date_format(register_time,'%Y%m%d')as reg_day ,register_time  from user_info  where job ...

/*题意：试卷得分按分界点[90,75,60]分为优良中差四个得分等级,未完成过试卷的用户无需输出 题解： 1.首先，将存在试卷考试记录的所有得分和用户进行等级划分,后续需要计算得分等级占比，所以需要exam_id分散开 2. 各个等级用户的得分登记占比，相当于为统计出各个等级用户的优良中差次数占比总数 */ select b.level ,b.score_grade ,round((b.num/sum(b.num)over(partition by b.level)),3) as rate from (   &n...

/*条件1： 只要有一个0级用户未完成试卷数大于2时    输出1：所有0级用户的试卷未完成数和未完成率   条件2：条件1不存在时   输出2：所有用户的试卷未完成数与未完成率 注意：存在有人没有作答，但是是已注册用户,所以如果使用sum(case when submit_time is null then 1 else 0 end)时会出现数据统计失误 所以，尽量使用count()*/  with a as  (  select u.uid, u.level , count(sta...

/*思路：先找出各个月份新增用户   1.先求出各个用户的最早活动月份，作为子循环   2.再在外部循环计算出各个月份出现的用户数极为每月新增用户   可能会出现有些月份无新增，所以不会体现出来，这个时候需要用到左连接，计算出月活数   */ select c.start_month ,c.mau ,coalesce(mon_add_cnt,0) ,max(mon_add_cnt) over(order by start_month) as max_month_add_uv ,sum(mon_a...

/* 试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。 --1.sql试卷未完成率较高的50%用户 --未完成率= 未完成试卷数/试卷总数; --计算出未完成试卷数，试卷总数，运用窗口函数，以未完成率排序降序，得到名次 --满足未完成率较高的50%的用户，所以，满足排名<= 0.5*总用户数 --2.6.7级用户的近三个月作答记录，每个月的答题数目与完成数目 --先找出三个月的做答记录,分别计算出答题数目和完成数目 --运用表连接，找出level>=6的用户数*/ select  b.ui...

# 2020年上半年的作答次数与排名信息作为左表，计算年度同比时需要有2020年2021年的数据指标 select c.tag , c.exam_2020cnt , d.exam_2021cnt , concat(round(((d.exam_2021cnt/c.exam_2020cnt)-1)*100,1),'%') as growth_rate , c.exam_2020_rank , d.exam_2021_rank , (cast(d.exam_2021_rank as SIGNED) - cast(c.exam_2020_rank as SIGNED)) as rank_delta ...

#1. 先找出满足SQL,hard, level为7 的用户id，作为筛选userid的条件 #2. 定位满足1 的uid后，计算出完成的考试数，注意需要满足2021年的约束，否则计算出的结果不对 #3. 左连接练习问题表，计算出完成问题，输出 SELECT b.uid,b.exam_cnt,count(p.question_id)as question_cnt FROM ( select r1.uid, count(r1.submit_time)as exam_cnt from exam_record r1 where r1.uid in (select r.uid from exam_re...

select i2.tag,count(*) as tag_cnt from ( select distinct a.uid from (select uid,date_format(e.submit_time,'%Y%m') as month ,count(e.submit_time) as cnt from exam_record e left join examination_info i on e.exam_id = i.exam_id group by 1,2 having count(e.submit_time) >= 3 )a #首先找出月均答题数超过3次的用户 )a1 #...

#注意活跃用户计算的时候时按照天来求和的，同一个用户可能在一天内活跃多次，需要去重 select date_format(start_time,'%Y%m')as month ,round(count(distinct uid, date_format(submit_time,'%Y%m%d'))/count(distinct if(score is not null,uid,null)) ,2)as avg_act_days ,count(distinct if(score is not null,uid,null)) as mau from exam_record where YEAR(s...

select count(*) as total_pv,count(submit_time) as complete_pv ,count(distinct if(submit_time is not null ,exam_id,null)) as examcnt from exam_record

首先，将科目表与用户信息分数表连接起来，明确好必要条件 接着，即可求取对应的得分值 select a.tag,a.difficulty, round(avg(a.scoren),1) as clip_avg_s from ( select r.uid,e.exam_id,e.tag,e.difficulty,e.duration,r.score as scoren from examination_info e join exam_record r on e.exam_id = r.exam_id where e.difficulty = 'hard' and e.exam_id = '900...

  我正在阅读专刊《校招面试考点全解析——数据分析师篇_牛客专刊》，快来和我一起学习吧 https://www.nowcoder.com/tutorial/10080/index

# 时间做差函数 timestampdiff(unit,time1,time2) # unit 一般可以为second,minute,hour,day,month,year DELETE FROM exam_record WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5 and score < 60

首先,得出题目的难度分布表 接着，与用户信息表连接后提取出计算的均值 #注意信息表的连接可能会出现null，所以明确好right join 还是left join select u.university ,a.difficult_level ,count(u.answer_cnt)/ count(distinct a.device_id) as avg_acnt from user_profile u right join ( select q.device_id,q.question_id,qd.difficult_level from question_practice_detail q ...