GuaMiao

分类

/ 注册

全部文章（共80篇）

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

# 先找到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 10

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

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

2024-03-22

0 50

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

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 59

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

# 先找到满足要求的用户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 62

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

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

2023-12-10

0 129

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

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

2023-10-30

0 161

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

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 118

题解 | 统计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 156

给出employees表中排名为奇数行的first_name

# 先将数据原来顺序进行排名，以及按照first_name升序进行排名得到 with t1 as ( select first_name, ROW_NUMBER() OVER() as t_rank1, ROW_NUMBER() OVER (ORDER BY first_name) as t...

2023-10-27

0 132

题解 | #国庆期间每类视频点赞量和转发量#

# 先找到2021/9/25到2021/10/03的数据 with t1 as ( select b.tag, a.if_like, a.if_retweet, a.end_time from tb_user_video_log as a left join tb_video...

2023-10-26

0 147