烛少

题解

/ 注册

全部文章 / 题解（共52篇）

题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

with a as( select user_id,sales_date from sales_tb st group by user_id,sales_date order by user_id,sales_date) select user_id,days_count from (select ...

Mysql

2022-01-20

0 250

题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

with a as( select user_id,sales_date from sales_tb st group by user_id,sales_date order by user_id,sales_date) select user_id,days_count from (select ...

Mysql

2022-01-20

0 316

题解 | #某宝店铺动销率与售罄率#

with a as( select item_id,sum(sales_num) s1,sum(sales_price) s2 from sales_tb group by item_id) select style_id,round(sum(s1)/sum(inventory-s1)*100,2)...

2022-01-20

0 273

题解 | #牛客直播各科目同时在线人数#

with st as ( select course_id,in_datetime from attend_tb at group by course_id,in_datetime ) select a.course_id,course_name,max(cnt) max_n...

Mysql

2022-01-19

1 572

题解 | #牛客直播各科目出勤率#

注重要的是理清逻辑 with a as( select course_id,user_id,case when sum(TIMESTAMPDIFF(MINUTE,in_datetime,out_datetime))>=10 then 1 else 0 end as attend from at...

Mysql

2022-01-19

0 358

题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#

with a as(select distinct author_id,answer_date from answer_tb) select c.author_id,author_level,max(days_cnt) days_cnt from (select author_id,sub,coun...

Mysql

2022-01-19

0 251

题解 | #列表 A,B中，对B中b1,b2对应同一个A中的a1的情况计数#

本题是比较经典的运用题，现实工作中比较常见，思路和方法有很多，以下仅供参考：方法一：使用group_concat函数，找出由author_id分组合并issue_type后的字符串中同时包含'Education'和'Career'的人数。需要使用locate函数，当字符串不存在于给定的字符中时，结...

Mysql

2022-01-19

20 780

题解 | #网易云音乐推荐(网易校招笔试真题)#

将所有表联结，注意a1.m1n is null这个条件 with a as( select m1.id m1d,m1.music_name m1n,m2.id m2d,m2.music_name m2n from follow f left join music_likes ml1 on f.use...

Mysql

2022-01-19

0 287

题解 | #最差是第几名(二)#

常规写法 select grade from (select grade, sum(number) over(order by grade)-number+1 lower, sum(number) over(order by grade) upper from class_grade)a wher...

Mysql

2022-01-18

0 238

题解 | #牛客的课程订单分析(五)#

使用first_value()\nth_value(),count()三个窗口函数即可 select user_id,first_buy_date,second_buy_date,cnt from ( select distinct user_id, first_value(date) over(p...

Mysql

2022-01-18

0 305