Formatted question description: https://leetcode.ca/all/1454.html

1454. Active Users

Level

Medium

Description

Table Accounts:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
the id is the primary key for this table.
This table contains the account id and the user name of each account.

Table Logins:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
There is no primary key for this table, it may contain duplicates.
This table contains the account id of the user who logged in and the login date. A user may log in multiple times in the day.

Write an SQL query to find the id and the name of active users.

Active users are those who logged in to their accounts for 5 or more consecutive days.

Return the result table ordered by the id.

The query result format is in the following example:

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

Logins table:
+----+------------+
| 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 table:
+----+----------+
| id | name     |
+----+----------+
| 7  | Jonathan |
+----+----------+
User Winston with id = 1 logged in 2 times only in 2 different days, so, Winston is not an active user.
User Jonathan with id = 7 logged in 7 times in 6 different days, five of them were consecutive days, so, Jonathan is an active user

Follow up question:

Can you write a general solution if the active users are those who logged in to their accounts for n or more consecutive days?

Solution

For each id, if there are at least consecutive values of login_date in table Logins, then id should be selected. Select such values of id and the corresponding values of name from Accounts table.

# Write your MySQL query statement below
select id, name from Accounts where id in (
    select l1.id from Logins l1, Logins l2, Logins l3, Logins l4, Logins l5
        where l1.id = l2.id and l2.id = l3.id and l3.id = l4.id and l4.id = l5.id
        and datediff(l2.login_date, l1.login_date) = 1
        and datediff(l3.login_date, l2.login_date) = 1
        and datediff(l4.login_date, l3.login_date) = 1
        and datediff(l5.login_date, l4.login_date) = 1
) order by id;

All Problems

All Solutions