Your question is Retention Rate 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.
Wise Energy wants to measure whether customers return to the product after signing up. Write a PostgreSQL query that calculates 30-day retention by signup cohort, using activity recorded in the Wise Energy product.
A user is retained if they perform at least one qualifying activity during days 1 through 30 after signup, inclusive. Use 2024-04-30 as the reporting date, so include only users whose full 30-day observation period has elapsed.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique Wise Energy user identifier |
| signup_date | DATE | Date the user signed up |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique activity event identifier |
| user_id | INT | User associated with the event |
| event_type | VARCHAR(50) | Type of Wise Energy activity |
| occurred_at | TIMESTAMP | Timestamp when the activity occurred |