写的无敌麻烦
写完借鉴下评论区各位大神的
还是把自己也上传了吧
#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