Rolling 7-Day Swipes Average | Dataford Interview Questions

Tinder's Data Science team wants a daily engagement metric for each user. Write a PostgreSQL query that calculates the rolling 7-day average number of swipes per user from Tinder swipe activity.

Requirements

Restrict activity to January 1 through January 10, 2025, including January 1 and excluding January 11.
Aggregate swipe events by user and calendar day before applying the window function.
Calculate the rolling 7-day average as the total swipes in the current date and preceding six calendar days divided by 7. Days without recorded swipes contribute zero.
Return results ordered by user_id and swipe date.

Schema

tinder_swipes

Column	Type	Description
swipe_idPK	INT	Unique swipe event identifier
user_id	INT	Tinder user who performed the swipe
swiped_at	TIMESTAMP	Timestamp when the swipe occurred
swipe_type	VARCHAR(20)	Swipe classification

Your question is Rolling 7-Day Swipes Average. 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

Tinder's Data Science team wants a daily engagement metric for each user. Write a PostgreSQL query that calculates the rolling 7-day average number of swipes per user from Tinder swipe activity.

Requirements

Restrict activity to January 1 through January 10, 2025, including January 1 and excluding January 11.
Aggregate swipe events by user and calendar day before applying the window function.
Calculate the rolling 7-day average as the total swipes in the current date and preceding six calendar days divided by 7. Days without recorded swipes contribute zero.
Return results ordered by user_id and swipe date.

Schema

tinder_swipes

Column	Type	Description
swipe_idPK	INT	Unique swipe event identifier
user_id	INT	Tinder user who performed the swipe
swiped_at	TIMESTAMP	Timestamp when the swipe occurred
swipe_type	VARCHAR(20)	Swipe classification

Your question is Rolling 7-Day Swipes Average. 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.