MEDIUMSolve on LeetCode
1454. Active Users
Problem Statement
Find the id and name of users who logged in on five or more consecutive days. Return the result table ordered by id.
Table Schema
Table: Logins
+-------------+------+
| id | int |
| login_date | date |
+-------------+------+
No primary key; may contain duplicate rows for the same id on the same date.
Table: Accounts
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key.
Examples
Example 1
Input Table:
Logins table:
+----+------------+
| id | login_date |
+----+------------+
| 1 | 2020-05-01 |
| 1 | 2020-05-02 |
| 1 | 2020-05-03 |
| 1 | 2020-05-04 |
| 1 | 2020-05-05 |
+----+------------+
Accounts table:
+----+-------+
| id | name |
+----+-------+
| 1 | Winston |
+----+-------+
Expected Output:
+----+---------+
| id | name |
+----+---------+
| 1 | Winston |
+----+---------+
Explanation: Classic 'gaps and islands' style consecutive-day problem.
SQL Solution
SELECT DISTINCT a.id, a.name
FROM Accounts a
JOIN Logins l ON a.id = l.id
WHERE EXISTS (
SELECT 1
FROM Logins l2
WHERE l2.id = l.id
AND l2.login_date BETWEEN DATE_SUB(l.login_date, INTERVAL 4 DAY) AND l.login_date
GROUP BY l2.id
HAVING COUNT(DISTINCT l2.login_date) = 5
)
ORDER BY a.id;
Problem Info
DifficultyMEDIUM
Topics
correlated-subquerygaps-and-islandsdate-functions
Reference Links