GuaMiao

分类

/ 注册

全部文章（共92篇）

题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

# 先找到每一个试卷作答用时第二快和第二慢的用时之差 with t1 as ( select exam_id, timestampdiff(minute, start_time, submit_time) as total_time, row_number() over(parti...

2024-05-23

0 291

题解 | #连续两次作答试卷的最大时间窗#

# 先找到2021年至少两天作答的用户 with t1 as ( select uid, id, date_format(start_time, '%Y-%m-%d') as day from exam_record where uid in ( select...

2024-05-18

0 274

题解 | #满足条件的用户的试卷完成数和题目练习数#

# 先找到SQL类试卷得分平均值>80并且是7级的用户 with t1 as ( select uid from exam_record where exam_id in (select exam_id from examination_info where tag =...

2024-05-13

0 273

题解 | #按照dept_no进行汇总#

SELECT dept_no, GROUP_CONCAT(emp_no SEPARATOR ',') AS employees FROM dept_emp GROUP BY dept_no;

2024-03-22

0 282

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

with t1 as ( select id, user_id, client_id, is_group_buy from order_info where date >= '2025-10-15' and status = 'completed' an...

2024-03-22

0 321

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

# 先找到满足要求的用户id with t1 as ( select user_id, count(id) as cnt from order_info where date >= '2025-10-15' and status = 'completed' ...

2024-03-22

0 315

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

# 动销率=每个货号已经销售的数量/（库存-已经销售的数量） # 售罄率 = GMV（即总销售额）/（吊牌价*库存数量） with t1 as ( select item_id, sum(sales_num) as sales_num, sum(sales_price) as gmv ...

2023-12-10

0 373

题解 | #牛客每个人最近的登录日期(五)#

# 先找到每一个用户的首次登陆时间 with t1 as ( select user_id, min(date) as first_date from login group by user_id ), t2 as ( # 然后看首次登陆之后的第二天该用户是否还登录...

2023-10-30

0 397

题解 | #牛客每个人最近的登录日期(六)#

with t1 as ( select a.name as u_n, b.date, b.number as ps_num from passing_number as b left join user as a on a.id = b.user_id ) selec...

2023-10-30

0 290

题解 | 统计salary的累计和running_total

select a.*, sum(salary) over(order by emp_no rows between unbounded preceding and current row) as running_total from ( select emp_no, salary f...

2023-10-27

0 352