牛客479560235号

分类

/ 注册

全部文章（共72篇）

题解 | 统计快递运输时长

SELECT t1.exp_type,ROUND(AVG(TIMESTAMPDIFF(second,out_time,in_time)/3600),1) time FROM express_tb t1 RIGHT JOIN exp_action_tb t2 ON t1.exp_number = t2...

2025-04-11

0 33

题解 | 完成员工考核试卷突出的非领导员工

with avg_exam AS ( SELECT exam_id,AVG(score) avg_score,AVG(timestampdiff(second,start_time,submit_time)) avg_time FROM exam_record GROUP ...

2025-04-10

0 32

题解 | 淘宝店铺的实际销售额与客单价

SELECT ROUND(SUM(t1.n_sales*t2.goods_price),3) sales_total, ROUND(SUM(t1.n_sales*t2.goods_price)/AVG(t1.n_users),3) per_trans FROM( SELECT goods_...

2025-04-10

0 32

题解 | 被重复观看次数最多的3个视频

SELECT t1.cid,t1.pv,ROW_NUMBER() OVER(ORDER BY t1.pv DESC,t2.release_date DESC) FROM ( SELECT b.cid, SUM(b.c_cnt) pv FROM ( ...

2025-04-09

0 54

题解 | 每天登陆最早的用户的内容喜好

SELECT a.date log_day,a.user_id,b.hobby FROM ( SELECT t1.user_id,t1.date FROM ( SELECT DATE(log_time) date,user_id,DENSE_RANK() OVER(p...

2025-04-07

0 36

题解 | 统计加班员工占比

SELECT t2.department,CONCAT(ROUND((COUNT(t1.staff_id)/COUNT(t2.staff_id))*100,1),'%') ratio FROM( SELECT staff_id,timestampdiff(second,first_clock...

2025-04-07

0 32

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

SELECT t4.name product_name, t3.total_sales, t3.category_rank FROM ( SELECT t1.product_id, SUM(t2.quantity) total_sales, DENSE_RANK(...

2025-03-25

1 43

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

SELECT AVG(score) FROM user_action_tb WHERE user_id IN( SELECT DISTINCT t1.rec_user FROM recommend_tb t1 INNER JOIN user_action_tb t2 ...

2025-03-24

0 26

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

SELECT * FROM( SELECT t1.user_id, t1.room_id, t2.room_type, CEIL(TIMESTAMPDIFF(HOUR,checkin_time,checkout_time)/24) days FROM...

2025-03-21

0 35

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

SELECT DATE_FORMAT(t1.t_time,'%Y-%m') time, SUM(t1.t_amount) total FROM trade t1,customer t2 WHERE t2.c_name = 'Tom' AND t1.t_time BETWEEN '2023-01-0...

2025-03-18

0 45