Write a SQL query to calculate user retention metrics, specifically identifying the percentage of users who interacted with an ad on day one and returned to purchase on day seven at Moloco. Use each user's cohort_date as day one, and treat cohort_date + 6 days as day seven. Use the users, ad_interactions, and purchases tables.
day_one_users, day_seven_purchasers, and day_seven_retention_pct.| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique Moloco user identifier |
| cohort_date | DATE | Date used as the user's day-one cohort date |
| cohort_segment | VARCHAR(30) | User acquisition segment |
| Column | Type | Description |
|---|---|---|
| interaction_idPK | INT | Unique ad interaction identifier |
| user_id | INT | User who interacted with the ad |
| interaction_date | DATE | Date of the ad interaction |
| device_type | VARCHAR(20) | Device used for the interaction |
| Column | Type | Description |
|---|---|---|
| purchase_idPK | INT | Unique purchase identifier |
| user_id | INT | User who made the purchase |
| purchase_date | DATE | Date of the purchase |
| amount | DECIMAL(10,2) | Purchase amount |
| channel | VARCHAR(20) | Purchase channel |