Your question is 7-Day Rolling Retention SQL. 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.
Andela Products wants to monitor whether users return after their initial product activity. Write a PostgreSQL query that calculates the 7-day rolling retention rate for each login date.
For this question, define daily 7-day retention as the percentage of distinct users who logged in on a given date and logged in again during the following 1 through 7 calendar days, inclusive. A user may generate multiple login events on the same date, but should count only once per daily cohort.
login_date, cohort_users, retained_users, and retention_rate, rounded to four decimal places.| Column | Type | Description |
|---|---|---|
| user_idPK | INTEGER | Unique Andela Products user identifier |
| display_name | VARCHAR(100) | User's display name |
| Column | Type | Description |
|---|---|---|
| event_idPK | INTEGER | Unique login event identifier |
| user_id | INTEGER | User associated with the login event |
| login_at | TIMESTAMP | Timestamp when the user logged in |