Given a table of email dispatches and a table of website purchases, write a query to calculate the conversion rate of users who made a purchase within 24 hours of receiving an email at Zulily.
Treat each user as converted if at least one purchase occurs from the email timestamp through 24 hours afterward, inclusive. Calculate the rate using distinct emailed users as the denominator.
converted_users, emailed_users, and conversion_rate_pct.| Column | Type | Description |
|---|---|---|
| dispatch_idPK | INT | Unique email dispatch identifier |
| user_id | VARCHAR(20) | Zulily user who received the email |
| sent_at | TIMESTAMP | Timestamp when the email was dispatched |
| Column | Type | Description |
|---|---|---|
| purchase_idPK | INT | Unique website purchase identifier |
| user_id | VARCHAR(20) | Zulily user who made the purchase |
| purchased_at | TIMESTAMP | Timestamp when the purchase was completed |