SQL Day-Over-Day Growth | Dataford Interview Questions

Instagram's Product Growth team wants to monitor engagement among the Creators user segment. Write a PostgreSQL query for February 1 through February 5, 2025 that calculates daily active Creators and their day-over-day growth.

Requirements

Generate one output row for every calendar date in the requested range, including dates with no Creator activity.
Count distinct Creator users active on each date. Multiple activity rows from the same user on a date must count once.
Use LAG to compare each day's active-user count with the previous calendar day.
Return growth as a percentage rounded to two decimals. Return NULL when there is no prior day or the prior day's count is zero.

Schema

users

Column	Type	Description
user_idPK	INT	Instagram user identifier
segment	VARCHAR(50)	Growth segment assigned to the user

instagram_daily_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User who generated the activity
activity_date	DATE	Calendar date of the activity

Your question is SQL Day-Over-Day Growth. 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