Consecutive Logins with Ranking | Dataford Interview Questions

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.

Requirements

Join session_logs to users to include the user's name.
Deduplicate multiple sessions from the same user on the same calendar date.
Use ROW_NUMBER() and date arithmetic to assign each consecutive sequence to a streak.
Return the streak start date, end date, and number of login days, ordered by user and streak start date.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name

session_logs

Column	Type	Description
session_idPK	INTEGER	Unique session identifier
user_id	INTEGER	User associated with the session
login_at	TIMESTAMP	Session start timestamp

Your question is Consecutive Logins with Ranking. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem