李齐亮

分类

/ 注册

全部文章（共31篇）

题解 | SQL166 #每天的日活数及新用户占比#

WITH t1 AS ( SELECT uid, in_time FROM tb_user_log UNION ALL SELECT uid, out_time AS in_time FROM tb_user_log...

2024-12-02

0 54

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

WITH t1 AS ( SELECT uid, in_time FROM tb_user_log UNION ALL SELECT uid, out_time AS in_time FROM tb_user_log...

2024-12-01

0 36

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

WITH t1 AS ( SELECT uid, in_time FROM tb_user_log UNION ALL SELECT uid, out_time AS in_time FROM tb_user_log...

2024-12-01

0 66

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

WITH t1 AS ( SELECT * FROM tb_user_log WHERE artical_id != 0 ), t2 AS ( SELECT uid, artical_id, in_time, ...

2024-11-30

0 40

题解 | #SQL162 2021年11月每天的人均浏览文章时长#

WITH t1 AS ( SELECT DATE(in_time) AS dt, SUM(TIMESTAMPDIFF(SECOND, in_time, out_time)) AS sum_viiew_len...

2024-11-30

0 31

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

WITH t1 AS ( SELECT DATE(MAX(end_time)) AS max_date, DATE_ADD(DATE(MAX(end_time)), INTERVAL -29 DAY) AS min_date FROM tb_user_video_lo...

2024-11-30

0 53

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

WITH t1 AS ( SELECT *, DATE (start_time) AS dt FROM tb_user_video_log WHERE DATE (start_time) BETWEEN '2021-09-25' AND '2021-1...

2024-11-30

1 34

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

WITH t1 AS ( SELECT tv.author, SUBSTR(start_time,1,7) AS month, CASE WHEN if_follow = 0 then 0 WHEN if_follow...

2024-11-20

1 39

题解 | #SQL158 每类视频近一个月的转发量/率#

WITH t1 AS ( SELECT DATE(MAX(start_time)) AS max_date, DATE_ADD(DATE(MAX(start_time)), INTERVAL -29 DAY) AS min_date FROM tb_user_vide...

2024-11-20

1 48

题解 | #SQL157平均播放进度大于60%的视频类别#

WITH t1 AS ( SELECT tag, ROUND(AVG(IF( TIMESTAMPDIFF(SECOND, start_time, end_time) / duration > 1, ...

2024-11-20

1 47