Your question is SQL for 30-Day DNA Kit Retention. Start with the requirements and the four 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.
Ancestry Marketing wants to evaluate 30-day retention for customers who purchased a DNA kit during the DNA-SUMMER-25 promotion. A retained customer is one who records at least one qualifying activity exactly 30 days after their first qualifying promotion purchase.
Write a PostgreSQL query that calculates the retention rate for this promotion.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique Ancestry user identifier |
| VARCHAR(255) | User email address | |
| acquisition_channel | VARCHAR(50) | Marketing channel attributed to the user |
| Column | Type | Description |
|---|---|---|
| promotion_idPK | INT | Unique promotion identifier |
| promotion_code | VARCHAR(50) | Promotion code used in campaign reporting |
| promotion_name | VARCHAR(100) | Readable promotion name |
| starts_at | DATE | Promotion start date |
| ends_at | DATE | Promotion end date |
| Column | Type | Description |
|---|---|---|
| purchase_idPK | INT | Unique purchase identifier |
| user_id | INT | User who made the purchase |
| promotion_id | INT | Promotion attributed to the purchase |
| product_category | VARCHAR(50) | Purchased product category |
| purchased_at | TIMESTAMP | Purchase timestamp |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique activity event identifier |
| user_id | INT | User associated with the activity |
| event_type | VARCHAR(50) | Type of Ancestry product activity |
| activity_at | TIMESTAMP | Activity timestamp |