Consecutive Login Days SQL | Dataford Interview Questions

How would you write a SQL solution for consecutive login days?

Write a PostgreSQL query that finds each user's longest streak of consecutive calendar days with at least one login. Multiple logins on the same day count once, and users without a valid login are excluded.

Output

One row per user with at least one valid login
Columns: user_id, user_name, streak_start_date, streak_end_date, and consecutive_days
Return the longest streak per user, breaking ties by the latest streak_end_date
Order by user_id ascending

Schema

users

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

login_events

Column	Type	Description
event_idPK	INT	Unique login event identifier
user_id	INT	User who generated the login event
login_at	TIMESTAMP	Timestamp when the login occurred

Your question is Consecutive Login Days SQL. 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