mysql查询周留存

出来了一个,但总是感觉不对。贴上来看看,如果不对应该怎么写?我要查询本周登录下周不再登录的用户数量。
select count(distinct playerId) from log_login l1 where week(now())-1=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW())=week(loginTime));

select count(distinct playerId) from log_login l1 where week(now())=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW()) - 1=week(loginTime));

留存率是:上周登录了 这周也登录了 你写的按理说没错

我要查询本周登录下周不再登录的用户数量。 不是下周 而是上周
select count(distinct playerId) from log_login l1 where week(now())-1=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW())=week(loginTime));

本周登录,下周不在登录的数据,这个问题感觉怪怪的。
看你的SQL,子查询中是查询本周的数据,
然后外部的查询是上周的数据。最后上周的id 存在于本周的id

select distinct playerId from log_login l1 where week(now())-1=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW())=week(loginTime));

这里不要count吧,不然你上周的结果就是聚合后的数字,跟本周的比不了吧。
要获取数量,
在外层在包一个 select count(1) from (
select distinct playerId from log_login l1 where week(now())-1=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW())=week(loginTime));
) t1

是这个意思不?