select uid ,date_format(dt,'%Y%m') AS month ,sum(case rk%7 when 3 then 3 when 0 then 7 else 1 end) AS coin from( select uid ,dt ,date_sub(dt,interval rn day) AS dt_fd ,dense_rank() over(partition by date_sub(dt,interval rn day),uid order by dt asc) AS rk from( select distinct uid ,date(in_time) AS d...

select dt ,count(distinct au.uid) AS dau ,round(count(distinct nu.uid)/count(distinct au.uid),2) AS uv_new_ratio from( select uid ,date(in_time) AS dt from tb_user_log union select uid ,date(out_time) AS dt from tb_user_log )au left join ( select uid ,date(min(in_time)) AS fd from tb_user_log group ...

select dt ,round(count(distinct act_uid) / count(distinct uid),2) AS uv_left_rate from( select first_date AS dt ,new_users.uid AS uid ,case when tl.uid is not null then tl.uid else null end AS act_uid from ( select uid ,date(min(in_time)) AS first_date from tb_user_log group by uid having date_forma...

select artical_id ,max(uv) AS max_uv from( select artical_id ,sum(flag) over(partition by artical_id order by act_time asc,flag desc) AS uv from( select artical_id ,in_time AS act_time ,1 AS flag from tb_user_log where artical_id <> 0 union all select artical_id ,out_time AS act_time ,-1 AS fl...

select author ,date_format(start_time,'%Y-%m') AS month ,round((sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0)))/count(start_time),3) AS fans_growth_rate /*截至当前粉丝总量=当月新增-当月掉粉+上月粉丝量*/ /*,sum(sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)) over(partition by author order by dat...

/*select date_sub(max(date(end_time)),interval 29 day) from tb_user_video_log where if_retweet = 1 */ select tag ,sum(if_retweet) AS retweet_cnt ,round(sum(if_retweet) / count(*),3) AS retweet_rate from tb_user_video_log tl inner join tb_video_info ti on tl.video_id = ti.video_id /*where datediff(da...

select er.uid ,ui.level ,ui.register_time /*,max(er.score) over(partition by er.uid) AS max_score*/ ,max(er.score) AS max_score from exam_record er inner join user_info ui on er.uid = ui.uid inner join examination_info ei on er.exam_id = ei.exam_id where ei.tag = '算法' and ui.job = '算法' and date(er.s...

select level ,score_grade ,round(count(*)/grade_cnt,3) AS ratio from( select level ,count(*) over(partition by level) AS grade_cnt ,case when score >= 90 then '优' when score>=75 and score<90 then '良' when score >=60 and score<75 then '中' else '差' end AS score_grade from exam_record er...

select uid ,incomplete_cnt ,incomplete_rate from( select ui.uid ,level ,count(if(submit_time is null,start_time,null)) AS incomplete_cnt ,count(start_time) AS complete_cnt /*有作答记录，用start_time*/ ,round(if(count(start_time)=0,0,count(if(submit_time is null,start_time,null))/count(start_time)),3) AS in...

/*活跃dau：按月count(distinct uid) 新增：按uid选min(start_time)*/ with t AS( select uid ,min(date_format(start_time,'%Y%m')) AS first_time from exam_record group by uid ) select start_month ,mau ,count(first_time) AS month_add_uv /*当月新增用户数，左连接不为null的数量*/ ,max(count(first_time)) over(order by start_month asc) ...

select exam_id ,date_format(start_time,'%Y%m') AS start_month ,count(id) AS month_cnt ,sum(count(id)) over(partition by exam_id order by date_format(start_time,'%Y%m')) AS cum_exam_cnt from exam_record group by exam_id ,date_format(start_time,'%Y%m') order by exam_id asc ,date_format(start_time,'%Y%...

select uid ,t1.exam_id ,round(avg(if(max_score = min_score,score,(score - min_score)/(max_score-min_score)*100)),0) AS avg_new_score from exam_record er inner join( select er.exam_id ,min(score) AS min_score ,max(score) AS max_score from exam_record er inner join examination_info ei on er.exam_id = ...

/*按年份把各类tag的作答完成情况情况和排名计算出来*/ WITH t AS( select tag ,year(submit_time) AS start_year ,count(submit_time) AS exam_cnt ,rank() over(partition by year(submit_time) order by count(submit_time) desc) AS exam_cnt_rank from exam_record er inner join examination_info ei on er.exam_id = ei.exam_id where subm...

/*筛选出SQL完成率后50%的6级或7级用户*/ with t3 AS( select t2.uid from( select uid ,percent_rank() over(order by round(count(submit_time)/count(start_time),1) asc) AS incomplete_rate_rank from exam_record where exam_id in( select exam_id from examination_info where tag = 'SQL' ) group by uid )t2 inner join user_i...