求职小张957

分类

/ 注册

全部文章（共39篇）

题解 | #统计活跃间隔对用户分级结果#

WITH t_test_user AS ( SELECT * ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt) AS ranking FROM( SELECT uid, artical_id, D...

2024-10-07

1 93

题解 | #2021年11月每天新用户的次日留存率#

SELECT dt ,ROUND(SUM(IF(DATEDIFF(next_active_date ,dt) = 1 , 1, 0))/COUNT(uid), 2) AS uv_left_rate FROM ( SELECT * ,DATE(in_ti...

2024-10-06

1 115

题解 | #每篇文章同一时刻最大在看人数#

WITH t_user_log AS ( SELECT artical_id ,in_time ,1 AS diff FROM tb_user_log WHERE artical_id != 0 UNION ALL SELECT artical_id ...

2024-10-06

1 88

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

SELECT video_id ,ROUND((100*rate + 5*like_num + 3*comment_num + 2*retweet_num)*fresh_rate, 0) AS hot_index FROM( SELECT video_id ...

2024-10-06

1 93

题解 | #国庆期间每类视频点赞量和转发量#

WITH t1 AS( SELECT tag, dt ,SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sum_like_cnt_7d ...

2024-10-06

1 90

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

SELECT author ,month ,fans_growth_rate ,SUM(fans) OVER(PARTITION BY author ORDER BY month) AS total_fans FROM( SELECT auth...

2024-10-05

1 61

题解 | #各用户等级的不同得分表现占比#

WITH t_tag_level as ( SELECT uid ,exam_id ,CASE WHEN score >=90 THEN "优" WHE...

2024-10-02

1 98

题解 | #筛选昵称规则和试卷规则的作答记录#

SELECT uid ,exam_id ,ROUND(AVG(score), 0) AS avg_score FROM user_info JOIN exam_record USING(uid) JOIN examination_info USING(exam_id)...

2024-10-01

1 78

题解 | #筛选限定昵称成就值活跃日期的用户#

SELECT uid ,nick_name ,achievement FROM user_info LEFT JOIN ( SELECT uid ,MAX(DATE_FORMAT(submit_time, "%Y%m")) ...

2024-10-01

0 96

题解 | #对试卷得分做min-max归一化#

SELECT uid ,exam_id ,ROUND(AVG(new_score)) AS avg_new_score FROM ( SELECT uid ,exam_id ,IF(max_score = min...

2024-09-28

0 76