Month-Over-Month Retention for SMS Users | Dataford Interview Questions

Calculate the month-over-month retention rate of users who interacted with an SMS message on Attentive.

Treat clicked and replied records in sms_interactions as qualifying interactions, and exclude delivered or NULL interaction types. Use users to include only recognized users. Exclude the latest interaction month because a subsequent month is not yet observable.

Output

One row per qualifying month with a subsequent observed month.
Columns: interaction_month, users_interacted, retained_next_month, and retention_rate.
retention_rate is the percentage of users who interacted again in the immediately following calendar month, rounded to two decimals.
Order by interaction_month ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Attentive user identifier
full_name	VARCHAR(100)	User display name

sms_interactions

Column	Type	Description
interaction_idPK	INT	Unique SMS interaction identifier
user_id	INT	User associated with the interaction
message_id	VARCHAR(50)	Attentive SMS message identifier
interacted_at	TIMESTAMP	Timestamp of the SMS event
interaction_type	VARCHAR(20)	Event type, such as clicked, replied, or delivered

Your question is Month-Over-Month Retention for SMS Users. 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