Juicelabxx

分类

/ 注册

全部文章（共38篇）

题解 | #各城市最大同时等车人数#

WITH a AS( SELECT city, event_time AS uv_time, 1 AS uv_cnt FROM tb_get_car_record UNION ALL SELECT city, end_time AS uv_time, -1 AS uv_cnt FROM tb_get...

2024-09-22

0 110

简洁为主 | #工作日各时段叫车量、等待接单时间和调度时间#

SELECT CASE WHEN DATE_FORMAT(event_time, '%H:%i:%s') >= '07:00:00' AND DATE_FORMAT(event_time, '%H:%i:%s') < '09:00:00' THEN '早高峰' ...

2024-09-22

0 100

题解 | #每个城市中评分最高的司机信息#

WITH a AS(SELECT city, driver_id, ROUND(SUM(grade)/COUNT(order_id), 1) AS avg_grade FROM tb_get_car_record r JOIN tb_get_car_order...

2024-09-21

0 100

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

WITH a AS ( SELECT DATE(event_time) AS dt, product_id FROM tb_order_overall JOIN tb_order_detail USING(order_id) JOIN...

2024-09-21

0 117

case when套case when｜#连续签到领金币#

WITH a AS ( SELECT uid, DATE(in_time) AS dt, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time) AS rn FROM tb_user_log WHERE artical_id = 0 AND sig...

2024-09-21

0 89

最蠢的方法 | #统计活跃间隔对用户分级结果#

WITH a AS( SELECT uid, in_time, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time DESC) AS rn, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_...

2024-09-21

0 95

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

SELECT video_id, ROUND((100 * full_play_rate + 5 * like_cnt+ 3 * comment_cnt + 2 * retweet_cnt) / (no_play_cnt + 1), 0) AS hot_index FROM( ...

2024-09-20

0 113

LIMIT6,3不行 | #国庆期间每类视频点赞量和转发量#

WITH a AS ( SELECT tag, DATE_FORMAT(start_time, '%Y-%m-%d' ) AS dt, COUNT(CASE WHEN if_like = 1 THEN l.id END) AS like_cnt, COUNT(CASE...

2024-09-20

0 91

存在同个用户多次进入直播的情况 | #牛客直播各科目出勤率#

SELECT course_id, course_name, ROUND(100 * COUNT(DISTINCT CASE WHEN timestampdiff(second, in_datetime, out_datetime) >= 10*60 THEN a.us...

2024-09-19

0 93

题解 | #每月及截止当月的答题情况#

WITH a AS ( SELECT uid, DATE_FORMAT(start_time, '%Y%m' ) AS start_month, MIN(DATE_FORMAT(start_time, '%Y%m')) OVER (PARTIT...

2024-09-18

0 107