Consecutive Activity Days Query | Dataford Interview Questions

Given a table of user interactions, write a query to find consecutive days of website activity using SQL window functions.

Return activity streaks containing at least two consecutive calendar days. Multiple interactions by the same user on one date should count as one active day, and rows with a missing interaction date should be excluded.

Output

One row per user streak with user_id, start_date, end_date, and consecutive_days.
Include streaks with at least two days.
Order by user_id, then start_date ascending.

Schema

user_interactions

Column	Type	Description
interaction_idPK	INT	Unique identifier for the interaction
user_id	INT	Identifier of the website user
interaction_date	DATE	Calendar date on which the interaction occurred
interaction_type	VARCHAR(30)	Type of website interaction

Your question is Consecutive Activity Days Query. Start with the requirements and the one table 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

Given a table of user interactions, write a query to find consecutive days of website activity using SQL window functions.

Output

One row per user streak with user_id, start_date, end_date, and consecutive_days.
Include streaks with at least two days.
Order by user_id, then start_date ascending.

Schema

user_interactions

Column	Type	Description
interaction_idPK	INT	Unique identifier for the interaction
user_id	INT	Identifier of the website user
interaction_date	DATE	Calendar date on which the interaction occurred
interaction_type	VARCHAR(30)	Type of website interaction

Your question is Consecutive Activity Days Query. Start with the requirements and the one table 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.