Consecutive Activity Using Lead Lag | Dataford Interview Questions

Publicis Groupe analysts need to identify consecutive days of activity across a digital platform. Write a PostgreSQL query that removes duplicate same-day interactions, uses LAG and LEAD to compare adjacent activity dates, and returns every activity day that belongs to a consecutive-day sequence.

Requirements

Treat multiple interactions by the same user on the same calendar day as one activity day.
Exclude rows where interaction_timestamp is NULL.
Use LAG and LEAD partitioned by user to identify activity days adjacent to another activity day.
Return the user, activity date, previous activity date, and next activity date, ordered by user and date.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
full_name	VARCHAR(100)	User's full name
platform	VARCHAR(50)	Publicis Groupe platform used by the user

user_interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction identifier
user_id	INT	User associated with the interaction
interaction_timestamp	TIMESTAMP	Timestamp when the interaction occurred

Your question is Consecutive Activity Using Lead Lag. 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