一、明确需求： 统计有用户互动的最近一个月，每类视频的转发量和转发率（保留3位小数） 二、需求分解: 最近一个月：(start_time - max(start_time))<=29 联结信息表info获得tag 根据tag分组，select计算 排序 三、实现代码： select b.tag , count(if(if_retweet = 1,a.id,null)) retweet_cut , round(count(if(if_retweet = 1,a.id,null)) / count(*),3) retweet_rate from (select ...

一、明确需求： 输出平均播放进度>60%的类别 二、需求分解 根据题例可知，计算互动表video_log每条记录的观看时长，联结信息表video_info获得每条记录对应的tag视频类别。 三、实现代码 with #对互动表和信息表进行去重处理 a as (select distinct * from tb_user_video_log) , b as (select distinct * from tb_video_info) # 此处唯一要注意的是观看时间的计算要用时间函数timestampdiff，不能(end_time-start_time)直接相减 select tag...

一、明确需求 题目需求： 统计6，7级的用户的总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数；按总活跃月份数、2021年活跃天数降序排序 二、思路分解 1、用户等级表选择level in (6,7) 得表t1 2、表2 select count(distinct if（）)结合计算各列 from (表t1左连试卷作答表，选择uid和start_time，新增标志列type='exam'） union all （表t1左连题目练习表，选择uid和submit_time,新增标志列type='practice') 3、按题目要求排序输出 三、代码...

一、明确需求 高难度SQL试卷得分平均值>80 and level = 7 的用户， 统计其2021年试卷总完成次数和题目总练习次数（剔除没有试卷完成记录的用户） 按试卷完成数升序 二、问题分解 作答表左连信息表，左连用户信息表，选择筛选条件，获得符合条件用户uid名单-表1 表1连试卷作答表，选择时间2021，分组计算，剔除没有完成记录用户得到表2 表1连接题目练习表，选择时间2021，分组计算得到表3 表2左连表3，排序 三、代码实现 with # 试卷作答表 左连 试卷信息表 左连用户信息表 获得用户uid名单 t1 as ( select a.uid from exa...

一、明确需求 2021年，所有试卷得分都达到85分 or （至少有一次用一半时间 and 高难度试卷 and 分数>80的id和活动号，按用户ID排序输出） 二、问题分解 记录表限制日期2021年，联结信息表获得难度和时间，得表1 对记录表按uid分组筛选符合条件1得表2，新增字段activity 表1where子句进行限制获得 输出，排序 三、代码实现 with # 记录表限制时间，联结信息表 t1 as ( select distinct a.*,b.difficulty,b.duration from exam_record a left join exami...

今天是成为“大橘已定”的牛牛了，距离“红名大佬”又近了一步！等级只证明个人的努力程度，知识的收获和技能的应用不予此成必然因果关系，不断学习并应用，不断复盘。春招不易，仍需继续努力，加油加油加油~！！！

第一天打卡

一、明确需求 统计每个题目和每份试卷作答人数和次数 二、问题拆解 分别计算每份试卷和每道题目的作答人数和次数，结果作为表1和表2 对表1和表2 union 连接 三、代码实现 方法1：排序好后在union联结 select * from ( select exam_id,count(distinct uid) uv,count(uid) pv from exam_record group by exam_id order by uv desc,pv desc )x union select * from ( select question_id,count(distinct ...

一、明确需求： 作答SQL类别的试卷得分>80的用户的等级分布 二、问题分解 1、筛选符合条件的用户 2、对输出进行调整 三、代码实现 select level,count(distinct t1.uid) level_cnt from ( select distinct uid from exam_record where score > 80 and exam_id in (select distinct exam_id from examination_info where tag = 'SQL') ) t1 left jo...

一、需求分析 计算当天5级以上SQL类别的试卷的作答人数UV，平均分avg_score 输出：exam_id,uv,avg_score （order by uv desc,avg_score asc) 二、思路 作答信息新增字段作答日期，内连接试卷表，选择考试tag为sql且试卷发行日期与作答日期相同的记录 内连接用户信息表，筛选等级>5的用户 处理输出结果：字段顺序，小数点，排序 三、代码实现 select t1.exam_id,count(distinct t1.uid) uv,round(avg(t1.score),1) avg_score from (selec...

一、明确需求 需求：2021年未完成试卷作答数大于1的有效用户数据 有效用户：试卷作答数>=1 and 未完成作答数<5 作答过的试卷tag集合detail：使用链接group_concat函数 输出标准：用户id，未完成试卷作答数，完成试卷作答数，作答过的试卷tag集合 二、思路 对exam_recoard选择2021年数据并去重作为原始表t1 基于t1以uid为分组条件，计算未完成作答数incomplete_cnt和完成作答数complete_cnt，结果作为表t2 对t2筛选符合条件的数据作为t3 由表t3和表t1内连接，同时连接表exam_info获取考试tag，计...

一、明确需求： 时间：2021年每月 month_q_cnt = 该月刷题记录总数 month_q_cnt = 用户总刷题数/该月总天数 2021汇总列 二、逻辑思路 用year(submit_time) =2021筛选数据 用date_format()提取‘年-月’字段作为后续分组条件，day(last_day())提取该月天数，作为后续mon_q_cnt计算 以年-月为分组条件，计算刷题总数和日均刷题数 以年为分组条件，计算汇总行，union合并3得到的结果 以submit_month为排列条件 with #选择2021，提取年，年-月，该月总天数 t1 as (select ...

一、明确需求： 每月完成作答的用户得平均活跃天数 MAU #剔除未提交试卷的记录；提取用户年份，年月，年月日作为后续的筛选条件 with t1 as ( SELECT distinct * , year(start_time) y , DATE_FORMAT(start_time,'%Y%m') y_m , date_format(start_time,'%Y%m%d') y_m_d from exam_record where submit_time is not null ) select y_m as month , round(avg(a...

一、明确需求 题目要求：找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。 需求解读：需要限制试卷类别为"SQL"，计算平均得分 观察数据：exam_record表只有exam_id没有“sql”类别，需要联结examination_info匹配tag进行限制。 二、解题思路 exam_record联结examination_info表，限制tag="SQL"作为源表t1 对t1根据tag为一组计算avg(score) 对2的结果作为where子查询的筛选条件，对整个结果取min(score) 三、代码实现 #筛选数据 with t1 as ( select a.*,b.ta...