Your question is SQL Retention Over 90 Days. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
Audiobooks category.category_name, cohort_users, retained_users, and retention_rate as a percentage rounded to two decimal places.| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique Scribd user identifier |
| Column | Type | Description |
|---|---|---|
| document_idPK | INT | Unique document identifier |
| category_name | VARCHAR(100) | Document category |
| 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 |