后街小行家
后街小行家
全部文章
分类
题解(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
首页
上一页
1
2
3
4
5
下一页
末页