SQL Retention Over 90 Days | Dataford Interview Questions

Write a SQL query to calculate the retention rate of users who engaged with a specific document category over the last 90 days at Scribd. Treat a user as retained if they have another Scribd engagement within 30 days after their first engagement with the category. Exclude users whose first category engagement occurred less than 30 days ago, because their retention window is incomplete.

Output

Return one row for the Audiobooks category.
Include category_name, cohort_users, retained_users, and retention_rate as a percentage rounded to two decimal places.
Include only users with a complete 30-day retention window.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Scribd user identifier

documents

Column	Type	Description
document_idPK	INT	Unique document identifier
category_name	VARCHAR(100)	Document category

engagements

Column	Type	Description
engagement_idPK	INT	Unique engagement identifier
user_id	INT	User who engaged with the document
document_id	INT	Document involved in the engagement
engaged_at	DATE	Date of the engagement

Your question is SQL Retention Over 90 Days. Start with the requirements and the three 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