Sustained Login Streak Users

MEDIUM20 pts

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:
1. id
2. name
Sort the result by id ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

logins =

[{"id":7,"login_date":"2020-05-30"},{"id":1,"login_date":"2020-05-30"},{"id":7,"login_date":"2020-05-31"},{"id":7,"login_date":"2020-06-01"},{"id":7,"login_date":"2020-06-02"},{"id":7,"login_date":"2020-06-02"},{"id":7,"login_date":"2020-06-03"},{"id":1,"login_date":"2020-06-07"},{"id":7,"login_date":"2020-06-10"}]

accounts =

[{"id":1,"name":"Winston"},{"id":7,"name":"Jonathan"},{"id":9,"name":"Mira"}]

Sustained Login Streak Users

Hints

Sustained Login Streak Users

Hints