JeremyYS

分类

/ 注册

全部文章（共10篇）

题解 | #所有用户对高难度题目的刷题数#

select a.device_id,convert(SUM(CASE WHEN c.difficult_level = 'hard' THEN 1 ELSE 0 END),signed) AS question_cnt FROM user_profile a LEFT JOIN question...

2024-03-27

0 159

题解 | #连续签到领金币#

--mySQL WITH log AS ( SELECT DISTINCT uid, DATE(in_time) AS dt FROM tb_user_log WHERE artical_id = 0 AND sign_in = 1 AND DATE(in_t...

2024-03-27

0 177

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

--- mySQL WITH tb as ( select uid, date (in_time) as dt from tb_user_log union ...

2024-03-27

0 178

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

--SQL SERVER with tb as ( select uid ,MIN(CONVERT(nvarchar(10),in_time,120)) as fistTime ,max(CONVERT(nvarchar(10),out_time,120)) as lastTime ...

2024-03-27

0 192

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

-- mySQL select first_in_time, round(count(t2.uid) / count(t1.uid), 2) as uv_rate from ( SELECT uid, min(...

2024-03-27

0 158

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

--mySQL with log as ( select uid, artical_id, in_time as dt, 1 is_in from ...

2024-03-26

0 129

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

--mySQL select date_format(a.in_time,"%Y-%m-%d") as dt,round(sum(timestampdiff(second,a.in_time,out_time))/count(distinct a.uid),1) as avg_v...

2024-03-26

0 186

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

select video_id ,CONVERT(int,(avg(if_comp*1.0) * 100 + 5 * sum(if_like*1.0) + 3 * sum(is_comment*1.0) + 2 * sum(if_retweet*1.0)) * (1.0 / (1 + mi...

2024-03-26

0 220

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

--SQL serverselect b.author,CONVERT(varchar(7),a.start_time,120) as month,ROUND(SUM(CASE WHEN a.if_follow=1 then 1when a.if_follow = 2 then -1 else 0 ...

2024-03-26

0 158

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

-- MySQL 答案 select * from ( SELECT b.tag, date_format (a.start_time, '%Y-%m-%d') as dt, SUM(sum(a....

2024-03-26

0 168