niuke题霸 SQL篇

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 牛客每个日期新用户的次日留存率