Sustained Login Streak Users

Medium

A product analytics team wants to detect consistently engaged users from raw authentication events. Table: accounts id: unique user identifier name: display name for the user Table: logins id: user identifier login_date: calendar date when the user logged in Duplicate rows can appear in this table Definition: A user is considered active when they logged in on at least 5 consecutive calendar days. Important semantics: Multiple login rows by the same user on the same date still count as one active day. Consecutive means each next date is exactly one day after the previous date. Task: Return all active users. Output requirements: Return columns in this exact order: id name Sort the result by id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

accounts:
| id | name     |
|----|----------|
| 1  | Winston  |
| 7  | Jonathan |
| 9  | Mira     |

logins:
| 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":7,"name":"Jonathan"}
]

Explanation

User 7 has a five-day streak from 2020-05-30 to 2020-06-03. Duplicate rows on 2020-06-02 do not increase streak length.

Example 2

Input

accounts:
| id | name |
|----|------|
| 10 | Ava  |
| 11 | Ben  |
| 12 | Cara |

logins:
| id | login_date |
|----|------------|
| 10 | 2024-01-28 |
| 10 | 2024-01-29 |
| 10 | 2024-01-30 |
| 10 | 2024-01-31 |
| 10 | 2024-02-01 |
| 11 | 2024-03-01 |
| 11 | 2024-03-03 |
| 11 | 2024-03-04 |
| 11 | 2024-03-05 |
| 11 | 2024-03-06 |
| 12 | 2024-06-10 |
| 12 | 2024-06-10 |
| 12 | 2024-06-11 |
| 12 | 2024-06-12 |
| 12 | 2024-06-13 |
| 12 | 2024-06-14 |

Output

[
  {"id":10,"name":"Ava"},
  {"id":12,"name":"Cara"}
]

Explanation

User 10 has a streak crossing a month boundary. User 12 qualifies despite duplicates. User 11 misses because 2024-03-02 is absent.

Example 3

Input

accounts:
| id | name |
|----|------|
| 21 | Noor |
| 22 | Sam  |
| 23 | Theo |

logins:
| id | login_date |
|----|------------|
| 21 | 2020-02-27 |
| 21 | 2020-02-28 |
| 21 | 2020-02-29 |
| 21 | 2020-03-01 |
| 21 | 2020-03-02 |
| 22 | 2020-12-29 |
| 22 | 2020-12-30 |
| 22 | 2020-12-31 |
| 22 | 2021-01-01 |
| 23 | 2021-01-05 |
| 23 | 2021-01-06 |
| 23 | 2021-01-07 |
| 23 | 2021-01-08 |
| 23 | 2021-01-09 |

Output

[
  {"id":21,"name":"Noor"},
  {"id":23,"name":"Theo"}
]

Explanation

Leap-day and year transitions are handled as normal calendar days. User 22 has only four consecutive days.

Constraints

1 <= number of rows in accounts <= 300000
0 <= number of rows in logins <= 800000
accounts.id is unique
logins.id references accounts.id
logins may contain duplicate rows
login_date is a valid YYYY-MM-DD date
A day counts once per user even if multiple login rows exist for that day
A user qualifies if they have a streak of at least 5 consecutive days
Return exactly two columns: id, name
Result must be sorted by id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exact columns: id, name

accounts: | id | name | |----|----------| | 1 | Winston | | 7 | Jonathan | | 9 | Mira | logins: | 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 |

accounts: | id | name | |----|------| | 10 | Ava | | 11 | Ben | | 12 | Cara | logins: | id | login_date | |----|------------| | 10 | 2024-01-28 | | 10 | 2024-01-29 | | 10 | 2024-01-30 | | 10 | 2024-01-31 | | 10 | 2024-02-01 | | 11 | 2024-03-01 | | 11 | 2024-03-03 | | 11 | 2024-03-04 | | 11 | 2024-03-05 | | 11 | 2024-03-06 | | 12 | 2024-06-10 | | 12 | 2024-06-10 | | 12 | 2024-06-11 | | 12 | 2024-06-12 | | 12 | 2024-06-13 | | 12 | 2024-06-14 |

accounts: | id | name | |----|------| | 21 | Noor | | 22 | Sam | | 23 | Theo | logins: | id | login_date | |----|------------| | 21 | 2020-02-27 | | 21 | 2020-02-28 | | 21 | 2020-02-29 | | 21 | 2020-03-01 | | 21 | 2020-03-02 | | 22 | 2020-12-29 | | 22 | 2020-12-30 | | 22 | 2020-12-31 | | 22 | 2021-01-01 | | 23 | 2021-01-05 | | 23 | 2021-01-06 | | 23 | 2021-01-07 | | 23 | 2021-01-08 | | 23 | 2021-01-09 |