神奇的大魔王在写周报

分类

/ 注册

全部文章（共9篇）

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

-- 请用SQL实现“2023年1月1日-2023年1月31日用户最长的连续登录天数” WITH q0 as (SELECT user_id, row_number() OVER(PARTITION BY user_id ...

2024-08-16

0 95

题解 | #查询连续登陆的用户#

with t1 as( select user_id,date_format(reg_time,'%Y-%m-%d') as fisrt_time, date_format(log_time,'%Y-%m-%d') as log_time1, ro...

2024-08-16

0 175

题解 | #统计各岗位员工平均工作时长#

SELECT post,round(avg(TIMESTAMPDIFF(second,first_clockin,last_clockin))/3600,3) work_hours FROM staff_tb s LEFT JOIN attendent_tb a ON s.staff_id= a.s...

2024-08-16

1 155

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

SELECT product_name, sum_quantity total_sales, rank() OVER(PARTITION BY category ORDER BY sum_quantity desc,product_id ) category_rank FROM (SELE...

2024-08-16

0 102

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

SELECT round(avg(distinct score),3) avg_score FROM( SELECT DISTINCT * FROM user_action_tb u LEFT JOIN recommend_tb r ON r.rec_user=u.user_id AND r.re...

2024-08-16

0 113

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

SELECT pay_ability, CONCAT(round(count(overdue_days) / count(1) * 100, 1),'%') as overdue_ratio FROM loan_tb l LEFT JOIN customer_tb c...

2024-08-15

2 102

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

SELECT user_id, c.room_id, room_type, datediff (checkout_time, checkin_time) as days FROM checkin_tb c LEFT JOIN guestroom_tb ...

2024-08-15

0 120

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

SELECT date_format (t_time, '%Y-%m') time, sum(t_amount) total FROM trade t LEFT join customer c ON t.t_cus = c.c_id WHERE c_name ...

2024-08-15

0 107

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

-- 从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲 SELECT * FROM ( SELECT month, row_number() OVER ( PARTITION BY month ORDER BY ...

2024-08-15

0 128