13. Active Users

Problem

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.

.....

Like the course? Get enrolled and start learning!

Christopher Guy Slater

· 11 days ago

-- this solution creates an anchor date for islands of consecutive login days
-- we ultimately group by id, name, and this anchor date 
-- and filter where the group is at least size 5

WITH distinct_logins AS (
  SELECT DISTINCT id, login_date
  FROM Logins
),
grouped AS (
  SELECT 
    id,
    login_date,
    DATE_SUB(login_date, INTERVAL 
      ROW_NUMBER() OVER(PARTITION BY id ORDER BY login_date) DAY) AS anchor
  FROM distinct_logins
)
SELECT g.id, a.name AS NAME
FROM grouped g 
JOIN Accounts a 
  ON g.id = a.id 
GROUP BY g.id, a.name, g.anchor
HAVING COUNT(*) >= 5
ORDER BY g.id;

Reading Progress