← All tasksSQL · Senior

The longest streak of consecutive days

For each user find the longest streak of consecutive days they logged in: columns user_id and longest_streak, ordered by user_id. Several logins on one day count as one day, and a streak can cross a month boundary.

Tables

CREATE TABLE logins (
  user_id int NOT NULL,
  day     date NOT NULL  -- several logins on the same day are possible
);

SELECT user_id, count(DISTINCT day) AS longest_streak
FROM logins
GROUP BY user_id
ORDER BY user_id;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (3)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO logins VALUES
  (1, '2024-01-01'), (1, '2024-01-02'), (1, '2024-01-03'),
  (1, '2024-01-05'), (1, '2024-01-06'),
  (2, '2024-01-10');

-- duplicate logins on one day and a streak across months
INSERT INTO logins VALUES
  (1, '2024-01-30'), (1, '2024-01-30'), (1, '2024-01-31'),
  (1, '2024-02-01'), (1, '2024-02-03'),
  (2, '2024-03-01'), (2, '2024-03-03'), (2, '2024-03-05');

-- the longest streak is the later one
INSERT INTO logins VALUES
  (5, '2024-06-01'),
  (5, '2024-06-10'), (5, '2024-06-11'), (5, '2024-06-12'), (5, '2024-06-13');