php - 活跃/非活跃用户

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 IS NULL 的非事件用户编写查询

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