1454.(Medium)活跃用户

表 Accounts:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是该表主键.
该表包含账户 id 和账户的用户名.

表 Logins:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.

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

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

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

结果表格式如下例所示:

Accounts 表:
+----+----------+
| id | name     |
+----+----------+
| 1  | Winston  |
| 7  | Jonathan |
+----+----------+

Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 7  | 2020-05-30 |
| 1  | 2020-05-30 |
| 7  | 2020-05-31 |
| 7  | 2020-06-01 |
| 7  | 2020-06-02 |
| 7  | 2020-06-02 |
| 7  | 2020-06-03 |
| 1  | 2020-06-07 |
| 7  | 2020-06-10 |
+----+------------+

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

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/active-users

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

两点需要注意

count()>=5这里不能用count(1)，否则同一天登录多次也会被计算进去
id前需要distinct，否则可能会有重复（不止一次连续登录5天以上）

with t as
(SELECT *,
DATE_SUB(login_date,interval DENSE_RANK() over(PARTITION by id ORDER BY login_date) day) ref
FROM logins)

SELECT distinct t.id, max(a.name) name
FROM t,Accounts a WHERE t.id = a.id
GROUP BY id, ref
HAVING COUNT(DISTINCT login_date)>=5;

Table Schema

Create table If Not Exists Accounts (id int, name varchar(10));
Create table If Not Exists Logins (id int, login_date date);
Truncate table Accounts;
insert into Accounts (id, name) values ('1', 'Winston');
insert into Accounts (id, name) values ('7', 'Jonathan');
Truncate table Logins;
insert into Logins (id, login_date) values ('7', '2020-05-30');
insert into Logins (id, login_date) values ('1', '2020-05-30');
insert into Logins (id, login_date) values ('7', '2020-05-31');
insert into Logins (id, login_date) values ('7', '2020-06-01');
insert into Logins (id, login_date) values ('7', '2020-06-02');
insert into Logins (id, login_date) values ('7', '2020-06-02');
insert into Logins (id, login_date) values ('7', '2020-06-03');
insert into Logins (id, login_date) values ('1', '2020-06-07');
insert into Logins (id, login_date) values ('7', '2020-06-10');

-- case 2
Truncate table Accounts;
insert into Accounts (id, name) values (182,"Gavriel");
insert into Accounts (id, name) values (119,"Naftali");
insert into Accounts (id, name) values (31,"Yaakov");
insert into Accounts (id, name) values (136,"Menachem");
insert into Accounts (id, name) values (142,"Sarah");
insert into Accounts (id, name) values (204,"Daniel");
insert into Accounts (id, name) values (49,"Ezra");
insert into Accounts (id, name) values (27,"David");
Truncate table Logins;
insert into Logins (id, login_date) values (142,"2020-6-27");
insert into Logins (id, login_date) values (119,"2020-6-29");
insert into Logins (id, login_date) values (31,"2020-6-26");
insert into Logins (id, login_date) values (27,"2020-6-27");
insert into Logins (id, login_date) values (182,"2020-7-2");
insert into Logins (id, login_date) values (136,"2020-6-28");
insert into Logins (id, login_date) values (142,"2020-7-5");
insert into Logins (id, login_date) values (27,"2020-6-29");
insert into Logins (id, login_date) values (136,"2020-6-27");
insert into Logins (id, login_date) values (49,"2020-7-1");
insert into Logins (id, login_date) values (204,"2020-7-1");
insert into Logins (id, login_date) values (49,"2020-7-5");
insert into Logins (id, login_date) values (204,"2020-7-3");
insert into Logins (id, login_date) values (49,"2020-7-3");
insert into Logins (id, login_date) values (31,"2020-7-3");
insert into Logins (id, login_date) values (204,"2020-7-3");
insert into Logins (id, login_date) values (142,"2020-6-30");
insert into Logins (id, login_date) values (119,"2020-6-26");
insert into Logins (id, login_date) values (142,"2020-6-29");
insert into Logins (id, login_date) values (136,"2020-7-2");
insert into Logins (id, login_date) values (49,"2020-7-2");
insert into Logins (id, login_date) values (182,"2020-7-4");
insert into Logins (id, login_date) values (119,"2020-6-29");
insert into Logins (id, login_date) values (49,"2020-6-30");
insert into Logins (id, login_date) values (136,"2020-7-5");
insert into Logins (id, login_date) values (27,"2020-7-2");
insert into Logins (id, login_date) values (136,"2020-6-28");
insert into Logins (id, login_date) values (31,"2020-6-29");
insert into Logins (id, login_date) values (204,"2020-7-3");
insert into Logins (id, login_date) values (142,"2020-6-29");
insert into Logins (id, login_date) values (31,"2020-6-30");
insert into Logins (id, login_date) values (204,"2020-6-27");
insert into Logins (id, login_date) values (204,"2020-7-2");
insert into Logins (id, login_date) values (182,"2020-6-27");
insert into Logins (id, login_date) values (31,"2020-7-3");
insert into Logins (id, login_date) values (119,"2020-7-4");
insert into Logins (id, login_date) values (142,"2020-6-27");
insert into Logins (id, login_date) values (119,"2020-6-27");
insert into Logins (id, login_date) values (27,"2020-6-26");
insert into Logins (id, login_date) values (142,"2020-7-2");
insert into Logins (id, login_date) values (27,"2020-6-28");
insert into Logins (id, login_date) values (136,"2020-6-26");
insert into Logins (id, login_date) values (119,"2020-6-27");
insert into Logins (id, login_date) values (142,"2020-7-1");
insert into Logins (id, login_date) values (27,"2020-7-1");
insert into Logins (id, login_date) values (31,"2020-6-29");
insert into Logins (id, login_date) values (204,"2020-6-28");
insert into Logins (id, login_date) values (136,"2020-6-28");
insert into Logins (id, login_date) values (204,"2020-7-3");
insert into Logins (id, login_date) values (31,"2020-6-28");
insert into Logins (id, login_date) values (182,"2020-6-29");
insert into Logins (id, login_date) values (49,"2020-7-4");
insert into Logins (id, login_date) values (204,"2020-6-27");
insert into Logins (id, login_date) values (136,"2020-7-5");
insert into Logins (id, login_date) values (142,"2020-7-4");
insert into Logins (id, login_date) values (31,"2020-7-2");
insert into Logins (id, login_date) values (182,"2020-7-1");
insert into Logins (id, login_date) values (204,"2020-6-28");
insert into Logins (id, login_date) values (31,"2020-7-4");
insert into Logins (id, login_date) values (136,"2020-7-1");
insert into Logins (id, login_date) values (136,"2020-6-26");
insert into Logins (id, login_date) values (27,"2020-7-4");
insert into Logins (id, login_date) values (27,"2020-6-29");
insert into Logins (id, login_date) values (31,"2020-7-2");

Previous1445.(Medium)苹果和桔子 Next1459.(Medium)矩形面积

Last updated 4 years ago

Was this helpful?

Create table If Not Exists Accounts (id int, name varchar(10)); Create table If Not Exists Logins (id int, login_date date); Truncate table Accounts; insert into Accounts (id, name) values ('1', 'Winston'); insert into Accounts (id, name) values ('7', 'Jonathan'); Truncate table Logins; insert into Logins (id, login_date) values ('7', '2020-05-30'); insert into Logins (id, login_date) values ('1', '2020-05-30'); insert into Logins (id, login_date) values ('7', '2020-05-31'); insert into Logins (id, login_date) values ('7', '2020-06-01'); insert into Logins (id, login_date) values ('7', '2020-06-02'); insert into Logins (id, login_date) values ('7', '2020-06-02'); insert into Logins (id, login_date) values ('7', '2020-06-03'); insert into Logins (id, login_date) values ('1', '2020-06-07'); insert into Logins (id, login_date) values ('7', '2020-06-10'); -- case 2 Truncate table Accounts; insert into Accounts (id, name) values (182,"Gavriel"); insert into Accounts (id, name) values (119,"Naftali"); insert into Accounts (id, name) values (31,"Yaakov"); insert into Accounts (id, name) values (136,"Menachem"); insert into Accounts (id, name) values (142,"Sarah"); insert into Accounts (id, name) values (204,"Daniel"); insert into Accounts (id, name) values (49,"Ezra"); insert into Accounts (id, name) values (27,"David"); Truncate table Logins; insert into Logins (id, login_date) values (142,"2020-6-27"); insert into Logins (id, login_date) values (119,"2020-6-29"); insert into Logins (id, login_date) values (31,"2020-6-26"); insert into Logins (id, login_date) values (27,"2020-6-27"); insert into Logins (id, login_date) values (182,"2020-7-2"); insert into Logins (id, login_date) values (136,"2020-6-28"); insert into Logins (id, login_date) values (142,"2020-7-5"); insert into Logins (id, login_date) values (27,"2020-6-29"); insert into Logins (id, login_date) values (136,"2020-6-27"); insert into Logins (id, login_date) values (49,"2020-7-1"); insert into Logins (id, login_date) values (204,"2020-7-1"); insert into Logins (id, login_date) values (49,"2020-7-5"); insert into Logins (id, login_date) values (204,"2020-7-3"); insert into Logins (id, login_date) values (49,"2020-7-3"); insert into Logins (id, login_date) values (31,"2020-7-3"); insert into Logins (id, login_date) values (204,"2020-7-3"); insert into Logins (id, login_date) values (142,"2020-6-30"); insert into Logins (id, login_date) values (119,"2020-6-26"); insert into Logins (id, login_date) values (142,"2020-6-29"); insert into Logins (id, login_date) values (136,"2020-7-2"); insert into Logins (id, login_date) values (49,"2020-7-2"); insert into Logins (id, login_date) values (182,"2020-7-4"); insert into Logins (id, login_date) values (119,"2020-6-29"); insert into Logins (id, login_date) values (49,"2020-6-30"); insert into Logins (id, login_date) values (136,"2020-7-5"); insert into Logins (id, login_date) values (27,"2020-7-2"); insert into Logins (id, login_date) values (136,"2020-6-28"); insert into Logins (id, login_date) values (31,"2020-6-29"); insert into Logins (id, login_date) values (204,"2020-7-3"); insert into Logins (id, login_date) values (142,"2020-6-29"); insert into Logins (id, login_date) values (31,"2020-6-30"); insert into Logins (id, login_date) values (204,"2020-6-27"); insert into Logins (id, login_date) values (204,"2020-7-2"); insert into Logins (id, login_date) values (182,"2020-6-27"); insert into Logins (id, login_date) values (31,"2020-7-3"); insert into Logins (id, login_date) values (119,"2020-7-4"); insert into Logins (id, login_date) values (142,"2020-6-27"); insert into Logins (id, login_date) values (119,"2020-6-27"); insert into Logins (id, login_date) values (27,"2020-6-26"); insert into Logins (id, login_date) values (142,"2020-7-2"); insert into Logins (id, login_date) values (27,"2020-6-28"); insert into Logins (id, login_date) values (136,"2020-6-26"); insert into Logins (id, login_date) values (119,"2020-6-27"); insert into Logins (id, login_date) values (142,"2020-7-1"); insert into Logins (id, login_date) values (27,"2020-7-1"); insert into Logins (id, login_date) values (31,"2020-6-29"); insert into Logins (id, login_date) values (204,"2020-6-28"); insert into Logins (id, login_date) values (136,"2020-6-28"); insert into Logins (id, login_date) values (204,"2020-7-3"); insert into Logins (id, login_date) values (31,"2020-6-28"); insert into Logins (id, login_date) values (182,"2020-6-29"); insert into Logins (id, login_date) values (49,"2020-7-4"); insert into Logins (id, login_date) values (204,"2020-6-27"); insert into Logins (id, login_date) values (136,"2020-7-5"); insert into Logins (id, login_date) values (142,"2020-7-4"); insert into Logins (id, login_date) values (31,"2020-7-2"); insert into Logins (id, login_date) values (182,"2020-7-1"); insert into Logins (id, login_date) values (204,"2020-6-28"); insert into Logins (id, login_date) values (31,"2020-7-4"); insert into Logins (id, login_date) values (136,"2020-7-1"); insert into Logins (id, login_date) values (136,"2020-6-26"); insert into Logins (id, login_date) values (27,"2020-7-4"); insert into Logins (id, login_date) values (27,"2020-6-29"); insert into Logins (id, login_date) values (31,"2020-7-2");