牛客479560235号

分类

/ 注册

全部文章（共50篇）

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

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 28

题解 | 被重复观看次数最多的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 41

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

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 28

题解 | 统计加班员工占比

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 25

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

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 32

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

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 22

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

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 29

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

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 38

题解 | 分析客户逾期情况

WITH t3 AS( SELECT t2.pay_ability, COUNT(t1.customer_id) cnt_c, SUM(IF(t1.overdue_days IS NULL,0,1)) s_c FROM loan_tb t1,customer...

2025-03-18

0 36

题解 | 最长连续登录天数

SELECT t3.user_id,MAX(t3.c_consec_days) max_consec_days FROM ( SELECT t2.user_id,t2.d_sub,COUNT(t2.d_sub) c_consec_days FROM( SELECT t...

2025-03-18

0 34