牛客238807874号

分类

/ 注册

全部文章（共10篇）

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

with t1 as ( select video_id, sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0)) as view_all, count(start_time) as view_amout, sum...

2024-09-06

0 174

题解 | #连续签到领金币#

with t1 as ( select uid, date(in_time) as log_day, row_number()over(partition by uid order by in_time) as rk from tb_user_log wher...

2024-09-06

0 111

题解 | #推荐内容准确的用户平均评分#

select round(sum(max_score)/count(distinct rec_user),3) as avg_score from (select rec_user,hobby_l,max(score) as max_score from recommend_tb as rt j...

2024-09-03

0 95

题解 | #每个商品的销售总额#

select product_name, total_sales, row_number()over(partition by category order by total_sales desc) as category_rank #排名函数，以类目分组，销售额降序 from (select...

2024-09-03

0 114

题解 | #查询培训指定课程的员工信息#

select staff_id,staff_name from staff_tb join cultivate_tb using(staff_id) where course like '%course3%' order by 1

2024-08-30

0 82

题解 | #查询连续入住多晚的客户信息？#

#计算日期需要+1，用timestampdiff函数 select user_id,room_id,room_type,timestampdiff(day,checkin_time,checkout_time)+1 as days from guestroom_tb join checkin_t...

2024-08-30

0 100

题解 | #获取指定客户每月的消费额#

select date_format(t_time,'%Y-%m') as time, sum(t_amount) as total from trade join customer on trade.t_cus = customer.c_id where c_name = 'Tom' and t...

2024-08-29

7 168

题解 | #分析客户逾期情况#

sum(if(overdue_days is not null,1,0)) 筛选逾期人数 concat(X...,'%')用于链接X...与% select pay_ability, concat(round(sum(if(overdue_days is not null,1,0))/count(...

2024-08-29

8 131

题解 | #最长连续登录天数#

select user_id, max(consec_days) as max_consec_days from ( select user_id, initial_day, count(...

2024-08-28

2 113

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

select month,ranking,song_name,play_pv from ( select month, row_number() over ( partition by ...

2024-08-28

0 109