Find Each User's Earliest Seven-Day Activity Streak
Company: Bytedance
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
# Find Each User's Earliest Seven-Day Activity Streak
Write one PostgreSQL SELECT statement or CTE query. Do not create, alter, or modify tables.
## Schema
user_activity
| column | type | description |
|---|---|---|
| activity_id | integer | Unique event |
| user_id | integer | User |
| activity_ts | timestamp | Event time |
## Task
Find users who were active on at least seven consecutive calendar dates. Multiple events on one date count as one active date. For each qualifying user, return the start date of that user's earliest qualifying seven-day streak.
## Required Output
Return user_id and streak_start. Sort by user_id ascending.
## Constraints
- A streak is based on calendar dates, not 24-hour gaps between timestamps.
- Longer streaks qualify; return the first date from which seven consecutive dates are present.
- Duplicate activity dates must not inflate a streak.
```hint Group consecutive dates into islands
After deduplicating user-date rows, subtract a row-number-based day offset. Consecutive dates share the same island key.
```
Quick Answer: A PostgreSQL interview problem about finding each user's earliest streak of at least seven consecutive active dates. It tests date deduplication, gaps-and-islands logic, streak length calculation, earliest-match selection, and deterministic output.