GuaMiao

分类

/ 注册

全部文章（共89篇）

题解 | #每个月Top3的周杰伦歌曲#

# 首先找到2022年18-25岁用户听周杰伦的歌的数据 with t1 as ( select month(fdate) as month, song_id from play_log where user_id in (select user_id from user_i...

2024-11-06

1 45

concat函数和concat_ws函数

# 主要考察concat函数，让字符串以特定分隔符连接起来 # concat函数和concat_ws函数的区别，前者分隔符在中间参数，后者分隔符是第一个参数 # select concat(last_name, "'", first_name) as name # from em...

2024-11-05

0 75

题解 | #统计活跃间隔对用户分级结果#

with t1 as (# 找到最近一天 select max(date_format(out_time, '%Y-%m-%d')) as today from tb_user_log ),# 新晋用户（近七天新增） t2 as ( select uid from (...

2024-07-01

0 154

题解 | #每类视频近一个月的转发量/率#

# 先找到近一个月每个视频的播放量和转发量 with t1 as ( select video_id, count(*) as watch_cnt, sum(if_retweet) as retweet_cnt from tb_user_video_log where dat...

2024-06-14

0 176

题解 | #店铺901国庆期间的7日动销率和滞销率#

# 可以分类讨论，对于9-25--10-01 with t1 as ( select count(product_id) as release_cnt from tb_product_info where shop_id = '901' and date_format...

2024-06-13

0 173

题解 | #近一个月发布的视频中热度最高的top3视频#

# 先找到近一个月的记录,并且使用case when看每一个记录是否完全播放 with t1 as ( select a.uid, a.video_id, a.if_follow, a.if_like, b.release_time, a.if_retweet, a.comment_id, ...

2024-06-11

0 172

题解 | #查找在职员工自入职以来的薪水涨幅情况#

# 先找到仍在职员工的最开始薪水和最后一次调整后的薪水、 with t1 as ( select c.emp_no, c.start_salary, d.salary as end_salary from salaries as d right join ( ...

2024-05-25

0 167

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

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

2024-05-23

0 203

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

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

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

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