Find Each User's Earliest Seven-Day Activity Streak

Problem Statement

You are given a table named user_activity containing log data of user interactions on a platform. A user may have multiple activity records on the same day.

Write a PostgreSQL query to identify each user's earliest streak of at least 7 consecutive active days. For each qualifying user, return their user_id, the start_date of the streak, and the end_date of that streak.

Rules

• A "streak" consists of consecutive calendar days where the user had at least one activity record on each day.
• If a streak lasts longer than 7 consecutive days, return the actual full streak's start and end dates (not just the first 7 days).
• If a user has multiple qualifying streaks (each >= 7 days), return only the one with the earliest start_date.
• If a user has no streak of at least 7 consecutive days, exclude them from the output.
• Order the final result by user_id in ascending order.

Schema

CREATE TABLE user_activity (
    user_id     INT,
    active_date DATE
);

Example Input

| user_id | active_date |
|---------|-------------|
| 1       | 2024-01-01  |
| 1       | 2024-01-01  |
| 1       | 2024-01-02  |
| 1       | 2024-01-03  |
| 1       | 2024-01-04  |
| 1       | 2024-01-05  |
| 1       | 2024-01-06  |
| 1       | 2024-01-07  |
| 1       | 2024-01-08  |
| 1       | 2024-02-01  |
| 2       | 2024-01-01  |
| 2       | 2024-01-02  |
| 2       | 2024-01-03  |
| 3       | 2024-03-01  |
| ... (7 consecutive days) ...

Example Output

| user_id | start_date  | end_date    |
|---------|-------------|-------------|
| 1       | 2024-01-01  | 2024-01-08  |
| 3       | 2024-03-01  | 2024-03-07  |

Explanation

• User 1 has duplicate logs on 2024-01-01, which should be collapsed into a single active day. User 1 maintains a continuous run from 2024-01-01 to 2024-01-08 (8 consecutive days >= 7), so the full range 2024-01-01 to 2024-01-08 is returned.
• User 2 only has 3 consecutive days, so they do not qualify.
• User 3 meets the 7-day threshold and is included.