活动/非活动用户

Question

我想检查谁都是活跃用户(他们都在过去7天内登录)，谁都是非活跃用户(他们在过去7天内没有登录)。我有2个表，用户和代理。用户的表结构如下：

id  | first_name | email | agency_id | last_login
 __________________________________________________
 1     Sujith      suj@suj   19865      2018/03/01
 2     Naveen      nav@nav   15098      2018/02/09
 3     Salin       sal@sal   18452      2017/12/06

代理的表结构如下：

id  | first_name |    email |  name |       last_login
 _________________________________________________________________
19865     Sujith      suj@suj  Yatra Travels 2018/03/01
19865     Raghav      rag@rag  Yatra Travels 2018/02/09
15098     Naveen      nav@nav  Travels Ltd   2017/12/06
15098     Monika      mon@mon  Travels Ltd   2017/12/06
18452     Raghu       rag@rag  MK Travels    

用户表包含主代理详细信息，代理表包含为代理工作的员工。因此，查询必须检查哪些代理已登录7天(活动用户)，哪些代理已7天未登录(非活动用户)。我已经为活跃用户写了一个查询。我需要重新检查它是否是一个正确的查询。如何为7天内未登录或last_login为空的非活动用户编写查询

SELECT users.first_name, users.email, users.last_name, users.last_login, agencies.name, 
agencies.id as agencyid FROM users  INNER JOIN agencies ON users.agency_id=agencies.id 
where users.last_login between adddate(now(),-7) and now() group by agencies.name 
order by agencies.name

有谁能帮帮我吗？提前谢谢。

Answer 1

​

where users.last_login >= DATE(NOW()) - INTERVAL 7 DAY group by agencies.name 
order by agencies.name

​

Answer 2

针对活动用户尝试以下查询

SELECT users.first_name, users.email, users.last_name, users.last_login, agencies.name, agencies.id as agencyid FROM users  INNER JOIN agencies ON users.agency_id=agencies.id where users.last_login between SUBDATE(now(),interval 7 DAY)  and now() group by agencies.name order by agencies.name

和非活动用户

SELECT users.first_name, users.email, users.last_name, users.last_login, agencies.name, agencies.id as agencyid FROM users  INNER JOIN agencies ON users.agency_id=agencies.id where (users.last_login < SUBDATE(now(),interval 7 DAY) or users.last_login is null) group by agencies.name order by agencies.name