NovaPlay wants to measure how well each acquisition channel retains new users after signup. Write a PostgreSQL query to calculate 30-day retention by acquisition channel.
A user is considered retained if they have at least one activity event on or after signup_date + 30 days and on or before signup_date + 60 days. Use each user's acquisition channel from the acquisitions table.
'Unknown'.users| column | type | description |
|---|---|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | User signup date |
| country | VARCHAR(50) | User country |
acquisitions| column | type | description |
|---|---|---|
| acquisition_id | INT | Unique acquisition record |
| user_id | INT | User identifier |
| channel | VARCHAR(50) | Acquisition channel |
| campaign_name | VARCHAR(100) | Campaign name |
activity_events| column | type | description |
|---|---|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User identifier |
| event_date | DATE | Activity date |
| event_type | VARCHAR(50) | Event type |
users| user_id | signup_date | country |
|---|---|---|
| 4 | 2024-01-20 | CA |
| 1 | 2024-01-01 | US |
| 7 | 2024-02-10 | US |
| 2 | 2024-01-05 | US |
| 9 | 2024-02-20 | GB |
| 3 | 2024-01-10 | GB |
| 6 | 2024-02-01 | CA |
| 8 | 2024-02-15 | US |
| 5 | 2024-01-25 | US |
| 10 | 2024-03-01 | CA |
acquisitions| acquisition_id | user_id | channel | campaign_name |
|---|---|---|---|
| 101 | 1 | Paid Search | Brand SEM |
| 102 | 2 | Organic Search | SEO Landing |
| 103 | 3 | Paid Social | Winter Prospecting |
| 104 | 4 | Referral | Invite Program |
| 105 | 5 | Paid Search | Generic SEM |
| 106 | 6 | Reactivation Blast | |
| 107 | 7 | Organic Search | Blog CTA |
| 108 | 8 | Paid Social | Video Ads |
| 109 | 9 | Unattributed Import | |
| 110 | 11 | Affiliate | Partner Network |
activity_events| event_id | user_id | event_date | event_type |
|---|---|---|---|
| 1001 | 1 | 2024-01-15 | session |
| 1002 | 1 | 2024-02-05 | purchase |
| 1003 | 2 | 2024-02-04 | session |
| 1004 | 2 | 2024-02-10 | purchase |
| 1005 | 3 | 2024-02-08 | session |
| 1006 | 4 | 2024-02-19 | session |
| 1007 | 5 | 2024-03-01 | session |
| 1008 | 6 | 2024-03-05 | purchase |
| 1009 | 7 | 2024-03-12 | session |
| 1010 | 8 | 2024-03-20 | session |
| 1011 | 9 | 2024-03-25 | purchase |
| 1012 | 12 | 2024-03-10 | session |
| channel | total_users | retained_users | retention_rate_pct |
|---|---|---|---|
| 1 | 1 | 100.00 | |
| Organic Search | 2 | 2 | 100.00 |
| Unknown | 2 | 1 | 50.00 |
| Paid Search | 2 | 1 | 50.00 |
| Paid Social | 2 | 0 | 0.00 |
| Referral | 1 | 0 | 0.00 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| country | VARCHAR(50) | User country |
| Column | Type | Description |
|---|---|---|
| acquisition_idPK | INT | Unique acquisition record identifier |
| user_id | INT | User tied to the acquisition record |
| channel | VARCHAR(50) | Acquisition channel |
| campaign_name | VARCHAR(100) | Marketing campaign name |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique activity event identifier |
| user_id | INT | User who generated the event |
| event_date | DATE | Date of the activity event |
| event_type | VARCHAR(50) | Type of user activity |
{"users":[[4,"2024-01-20","CA"],[1,"2024-01-01","US"],[7,"2024-02-10","US"],[2,"2024-01-05","US"],[9,"2024-02-20","GB"],[3,"2024-01-10","GB"],[6,"2024-02-01","CA"],[8,"2024-02-15","US"],[5,"2024-01-25","US"],[10,"2024-03-01","CA"],["1","2024-01-08","NO"],["2","2024-01-08","null"],["3","2024-03-01","JP"],["4","2024-02-12","US"],["5","2024-01-21","AU"],["6","2024-02-04","NO"],["7","2023-12-29","null"],["8","2024-01-17","DK"],["9","2024-02-04","NL"],["10","2024-02-04","DK"],["11","2024-01-03","SE"]Output[["Content Marketing","1","1","100.00"],["Email","1","1","100.00"],["Influencer","1","1","100.00"],["Organic Search","2","2","100.00"],["Paid Search","2","2","100.00"],["Referral","1","1","100.00"],["SMS","1","1","100.00"],["Paid Social","2","1","50.00"],["Unknown","9","2","22.22"],["Affiliate","1","0","0.00"]]