1454. Active Users π
Description
Table: Accounts
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ id is the primary key (column with unique values) 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 | +---------------+---------+ This table may contain duplicate rows. 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.
Active users are those who logged in to their accounts for five or more consecutive days.
Write a solution to find the id and the name of active users.
Return the result table ordered by id
.
The result format is in the following example.
Example 1:
Input: 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 | +----+------------+ Output: +----+----------+ | id | name | +----+----------+ | 7 | Jonathan | +----+----------+ Explanation: 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: Could you write a general solution if the active users are those who logged in to their accounts for n
or more consecutive days?
Solutions
Solution 1: Using Window Functions
First, we join the Logins
table and the Accounts
table, and remove duplicates to get the temporary table T
.
Then, we use the window function ROW_NUMBER()
to calculate the base login date g
for each user id
. If a user logs in for 5 consecutive days, their g
values are the same.
Finally, we group by id
and g
to count the number of logins for each user. If the number of logins is greater than or equal to 5, then the user is considered active.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
|