题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
写的无敌麻烦
写完借鉴下评论区各位大神的
还是把自己也上传了吧
#sql第70
select
c.date
,round(ifnull(count(d.user_id),0),3)
from(
select
distinct date
from login
) c
left join (
select
a.user_id
,a.md
,l.date
from (
select
user_id
,min(date) md
from login
group by user_id
) a
left join login l
on a.user_id = l.user_id
where date_add(md,interval 1 day) = l.date
) d
on c.date = d.md
group by c.date
每日新用户次日登陆
select
a.date
,ifnull(count(b.user_id),0) s1
from (
select
distinct date
from login
) a
left join (
select
user_id
,min(date) m
from login
group by user_id
) b
on a.date = b.m
group by a.date
#每天新用户登陆次数
select
a1.date
,ifnull(round(s2/s1,3),0)
from(
select
a.date
,ifnull(count(b.user_id),0) s1
from (
select
distinct date
from login
) a
left join (
select
user_id
,min(date) m
from login
group by user_id
) b
on a.date = b.m
group by a.date
)a1
join (
select
c.date
,ifnull(count(d.user_id),0) s2
from(
select
distinct date
from login
) c
left join (
select
a.user_id
,a.md
,l.date
from (
select
user_id
,min(date) md
from login
group by user_id
) a
left join login l
on a.user_id = l.user_id
where date_add(md,interval 1 day) = l.date
) d
on c.date = d.md
group by c.date
) a2
on a1.date = a2.date
