Longest streak of consecutive login days (gaps and islands)
HardP2Coding
Table logins:
| Column | Type |
|---|---|
| user_id | int |
| login_date | date |
A user can log in several times on one day (duplicate rows). For each user, find the length of their longest streak of consecutive calendar days with at least one login.
Output columns: user_id, longest_streak (in days).
Schema
CREATE TABLE logins ( user_id int NOT NULL, login_date date NOT NULL );
Example 1
- Input
- user 1: Jan 1, Jan 2, Jan 3, Jan 5, Jan 6
- Output
- (1, 3)
Constraints
- PostgreSQL dialect.
Hints
Give it a real attempt first. Run shows which examples fail.
Saved automatically
PostgreSQL
Run checks the 3 visible tests, Submit all 8. ⌘Enter runs, ⌘⇧Enter submits.