Data Cleaning and Rolling DAU | Dataford Interview Questions

Given a dataset of user pin interactions, write a Python Pandas script to clean the data, handle missing values, and calculate the rolling 7-day active user count.

For this SQL version, write a PostgreSQL query using the provided tables. Exclude interactions missing a user, pin, or timestamp, and include dates with no valid activity.

Output

One row per calendar date from the earliest to latest valid interaction.
Columns: activity_date, rolling_7_day_active_users.
Count distinct users active on the date or during the preceding six calendar days, ordered by activity_date ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Pinterest user identifier
display_name	VARCHAR(100)	Optional user display name
created_at	DATE	Date the user account was created

pin_interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction identifier
user_id	INT	User who performed the interaction
pin_id	VARCHAR(30)	Identifier of the interacted pin
action_type	VARCHAR(20)	Interaction type, such as save or click
occurred_at	TIMESTAMP	Timestamp when the interaction occurred

Your question is Data Cleaning and Rolling DAU. 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