后街小行家

分类

题解(10)

/ 注册

全部文章（共41篇）

题解 | #各用户活跃分层人数统计#

with cte as ( select uid,min(login_date) as reg_date, #注册时间 max(login_date) as rec_date, #最近活跃时间 max(max(log...

2024-03-23

0 202

题解 | #统计最大连续登录天数区间#

with cte as ( select distinct uid,login_date,dense_rank()over(order by login_date) as dk from user_login_tb ), cte1 as ( select uid,adddate(login_date...

2024-03-23

0 158

题解 | #找出待召回的流失用户#

with cte as ( select uid,count(*) as times, count(distinct login_date) as days, max(login_date) as rec_date, max(max(...

2024-03-23

0 127

题解 | #日活次日留存率和新户次日留存率#

with cte as ( select *,min(login_date)over(partition by uid) as reg_date, lead(login_date)over(partition by uid) as next_date from (select di...

2024-03-23

0 145

题解 | #计算每天的新用户占比#

#构造了三个临时表 with cte as ( select *,min(login_date)over(partition by uid) as min_date from user_login_tb ), cte1 as( select min_date as dt,count(distinct...

2024-03-23

0 127

题解 | #计算每个人的平均使用周期#

with cte as( select *,first_value(login_date)over(partition by uid order by login_date) as first_day, max(login_date)over() as last_date from...

2024-03-23

0 143

题解 | 开窗函数的强大！（在职员工入职以来薪资涨幅情况）

select emp_no,growth from ( select emp_no,to_date,last_value(salary)over(partition by emp_no order by from_date )-first_value(salary)over(partition by...

2023-10-14

0 260

题解 | #分别满足两个活动的人#

select uid, 'activity1' as activity from exam_record left join examination_info using(exam_id) where year(start_time)=2021 group by uid having min(sco...

2023-10-12

1 247

题解 | #未完成试卷数大于1的有效用户#

with cte as ( select uid,start_time,submit_time,tag,concat(date(start_time),':',tag) as deta from exam_record left join examination_info using(exam_id...

2023-10-11

0 177

题解 | #月总刷题数和日均刷题数#

select ifnull(submit_month,'2021汇总') as submit_month,month_q_cnt,avg_day_q_cnt from ( select date_format(submit_time,'%Y%m') as submit_month, count(*)...

2023-10-11

0 243