Your question is Consecutive Logins with Ranking. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
CACI International's analytics team is reviewing access patterns in a mission operations platform. Write a PostgreSQL query to identify users who logged in on consecutive calendar days.
The query should treat multiple sessions on the same calendar date as one login day. Return only streaks containing at least two consecutive login dates.
session_logs to users to include the user's name.ROW_NUMBER() and date arithmetic to assign each consecutive sequence to a streak.| Column | Type | Description |
|---|---|---|
| user_idPK | INTEGER | Unique user identifier |
| user_name | VARCHAR(100) | User display name |
| Column | Type | Description |
|---|---|---|
| session_idPK | INTEGER | Unique session identifier |
| user_id | INTEGER | User associated with the session |
| login_at | TIMESTAMP | Session start timestamp |