[MySQL] 연속으로 N번이상 반복되는 값 구하기2

테이블과 결과

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.

문제

  • Active users are those who logged in to their accounts for five or more consecutive days.
  • Write an SQL query to find the id and the name of active users.

답

SELECT
    l1.id AS 'id'
    ,(SELECT a.name FROM Accounts a WHERE l1.id = a.id) AS 'name'
FROM Logins l1
JOIN Logins l2
    ON  l1.id = l2.id 
    AND DATEDIFF(l2.login_date, l1.login_date) BETWEEN 1 AND 4
GROUP BY l1.id, l1.login_date 
HAVING COUNT(DISTINCT l2.login_date) = 4 
;

links

social