Medium之1454.活跃用户***

桃扇骨 2022-08-31 04:52 117阅读 0赞

**表 Accounts:**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

id 是该表主键.  
该表包含账户 id 和账户的用户名.

**表 Logins:**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>login_date</td> 
   <td>date</td> 
  </tr> 
 </tbody> 
</table>

该表无主键, 可能包含重复项.  
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.

### 问题 ###

写一个 SQL 查询, 找到活跃用户的 id 和 name.

活跃用户是指那些至少连续 5 天登录账户的用户.

返回的结果表按照 id 排序.

### 示例 ###

**Accounts 表:**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Winston</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Jonathan</td> 
  </tr> 
 </tbody> 
</table>

**Logins 表:**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>login_date</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>7</td> 
   <td>2020-05-30</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2020-05-30</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2020-05-31</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2020-06-01</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2020-06-02</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2020-06-02</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2020-06-03</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2020-06-07</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>2020-06-10</td> 
  </tr> 
 </tbody> 
</table>

**Result 表:**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>7</td> 
   <td>Jonathan</td> 
  </tr> 
 </tbody> 
</table>

id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.  
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/active-users  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

思路：  
首先，需要提取同列的顺序（时间）信息进行定量而不是定性（比如只考虑大小）的比对，这个时候就应该想到需要自连接。

我们把表logins使用join自连接，条件限制在id相等和日期差距在0到4天。

筛选之后按照l1的id和login\_date分组（login\_date分组的原因是，按照id分完的组中所有的日期只是满足和相应id的任意一个日期差距在0到4之间，这些日期并不一定是连续的，所以我们再按照参考表l1的日期再次分组）。

最后通过count筛选，在id相同的条件下，日期相差在0到4天而且互不重复的日期合计能达到5个，这五个日期必然是连续的。

最后在select环节还需要小心用户名id的重复出现。

select distinct l1.id,name
    from logins l1
        join logins l2 
        on l1.id=l2.id and datediff(l2.login_date,l1.login_date) between 0 and 4
        left join
        accounts a on l1.id=a.id 
    group by l1.id,l1.login_date
    having count(distinct l2.login_date) >=5
    order by l1.id