Your question is MoM Retention for Student Profiles. Start with the requirements and the two 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 month-over-month retention rate of active student profiles on Handshake - stryder.
Use student_profiles and profile_activity. Treat a profile as active in a month when it has at least one activity record during that month and its profile status is active. Retention for a month is the share of profiles active in the previous month that are also active in the current month.
month_start, prior_month_active_profiles, retained_profiles, and retention_rate.month_start ascending, with the rate rounded to four decimal places.| Column | Type | Description |
|---|---|---|
| profile_idPK | INT | Unique student profile identifier |
| profile_status | VARCHAR(20) | Current profile status |
| school_name | VARCHAR(120) | Student's school |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity record identifier |
| profile_id | INT | Student profile associated with the activity |
| activity_date | DATE | Date on which the activity occurred |
| activity_type | VARCHAR(30) | Type of profile activity |