SQL66 牛客每个人最近的登录日期(1):较简单
select user_id,max(date) as id
from login
group by user_id
order by user_id
窗口函数解法:
--第一种
select distinct
user_id,
last_value(date) over(partition by user_id order by date rows between current row and unbounded following) as d
from login;
select distinct
user_id,
first_value(date) over(partition by user_id order by date desc) as d
from login;
--第二种
select user_id,date
from(select user_id,date,
row_number() over(partition by user_id order by date desc) as rk
from login) t1
where rk=1
order by user_id
收藏题解:
SQL67 牛客每个人最近的登录日期(2)
注:子查询可以两个字段!
-- where子句
select user.name as u_n, client.name as c_n,login.date
from login join user on login.user_id=user.id
join client on login.client_id=client.id
where (login.user_id,login.date) in(select user_id,max(date)
from login group by login.user_id)
order by user.name;
--窗口函数
select u_n,c_n,date
from(select u.name as u_n,c.name as c_n,date,
rank() over(partition by l.user_id order by date desc) as rk
from login as l left join user as u on l.user_id=u.id
left join client as c on l.client_id=c.id) t1
where rk=1
order by u_n
SQL68 牛客新登录客户的次日成功的留存率
(第一天登录的新用户并且第二天也登录的用户)/(总用户)
select round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date) in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY)
from login group by user_id);
select round(count(distinct login.user_id)/(select count(distinct user_id) from login),3)
from login join(select user_id,min(date) as new_date
from login
group by user_id) new
on login.user_id=new.user_id
where timestampdiff(day,new_date,date)=1
select
round(sum(case when rank1=1 and timestampdiff(day,date,date2)=1
then 1
else 0
end)/count(distinct user_id),3) as p
from(select user_id,date,
row_number() over (partition by user_id
order by date) as rank1,
lead(date,1) over (partition by user_id
order by date) as date2
from login) as t1;
SQL 69 查询每个日期登录新用户个数
一、先找出用户的第一天登录的日期,再与登陆表连接,将登陆表中的日期和第一天登录的日期比较,如果相等,加一
但是有问题啊,如果用户第一天登录多次呢?
改进:去掉login表中(user_id,date)的重复数据再连接
select login.date,sum(if(login.date=new_date,1,0)) as new
from login join(select user_id,min(date) as new_date
from login
group by user_id) as t1
on t1.user_id=login.user_id
group by login.date
--改进
select t.date,sum(if(t.date=new_date,1,0)) as new
from (select distinct user_id,date from login) t
join(select user_id,min(date) as new_date
from login
group by user_id) as t1
on t1.user_id=t.user_id
group by t.date
二、窗口函数:用row_number可以保证不重复
select date,sum(if(rank1=1,1,0))
FROM(SELECT user_id,date,
row_number() over (partition by user_id order by date) as rank1
FROM login
) as t1
group by date
order by date
三 和第一个差不多
1.先得到所有日期表select distinct date from login
;2.然后左连接新用户首次登陆的日期表(select user_id, min(date) first_date from login group by user_id)
;3.归类统计日期出现的次数.
select a.date, count(b.user_id) new
from (select distinct date from login) a
left join (select user_id, min(date) first_date from login group by user_id) b on a.date=b.first_date
group by a.date
order by a.date
SQL70 牛客每个日期新用户的次日留存率