Your question is 30-Day Campaign User Retention. 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.
You are given campaign interaction data and subsequent user activity data. Write a PostgreSQL query to calculate the 30-day retention rate for users who interacted with a specific Google Ads campaign. Treat a user as retained if they have at least one activity event in the 30 days after their first interaction with that campaign. Return the campaign name, the number of users in the campaign cohort, the number of retained users, and the retention rate as a percentage rounded to two decimals.
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Primary key for the campaign |
| campaign_name | VARCHAR(100) | Google Ads campaign name |
| Column | Type | Description |
|---|---|---|
| interaction_idPK | INT | Primary key for the interaction event |
| user_id | INT | User who interacted with the campaign |
| campaign_id | INT | Referenced campaign identifier |
| interaction_date | DATE | Date of campaign interaction |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Primary key for the activity event |
| user_id | INT | User who generated the activity |
| activity_date | DATE | Date of user activity |
| activity_type | VARCHAR(50) | Type of activity event |