Daily Active Users and Retention | Dataford Interview Questions

Amazon Development Center U.S. product analytics needs a daily engagement report for an internal product surface. Write a PostgreSQL query that calculates daily active users and next-day retention from registered-user activity.

Requirements

Count each registered user at most once per activity date, even when the user generates multiple events.
Return daily active users, users active again on the following calendar day, and the next-day retention rate as a percentage.
Exclude activity rows without a matching registered user and return dates in ascending order.
Return NULL for the retention rate on the final activity date because no following day is available.

Schema

users

Column	Type	Description
user_idPK	INT	Registered user identifier
display_name	VARCHAR(100)	User display name

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User who generated the activity
activity_date	DATE	Calendar date on which the activity occurred
event_type	VARCHAR(30)	Type of product activity

Your question is Daily Active Users and Retention. 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