Skip to content

1454. Active Users 👍

 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
26
27
28
WITH
  DistinctLogins AS (
    SELECT DISTINCT * FROM Logins
  ),
  RankedLogins AS (
    SELECT
      *,
      DENSE_RANK() OVER(
        PARTITION BY id
        ORDER BY login_date
      ) AS `rank`
    FROM DistinctLogins
  ),
  RankedLoginsWithGroupId AS (
    SELECT
      *,
      DATE_ADD(login_date, INTERVAL -`rank` DAY) AS group_id
    FROM RankedLogins
  )
SELECT DISTINCT
  id,
  Accounts.name
FROM RankedLoginsWithGroupId
INNER JOIN Accounts
  USING (id)
GROUP BY Accounts.id, RankedLoginsWithGroupId.group_id
HAVING COUNT(*) >= 5
ORDER BY 1;