Skip to content
Onsite

Longest streak of consecutive login days (gaps and islands)

HardP2Coding

Table logins:

ColumnType
user_idint
login_datedate

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

PostgreSQL
Run checks the 3 visible tests, Submit all 8.