Notification Type Conversion
Start Timer
0:00:00
You are tasked to determine which type of notification generates the most conversions. Due to an overlook in the database design, there is no concrete way to attribute a purchase to a specific notification. However, you might be able to infer this data.
Given tables notifications, notification_events, and purchases, write a query to determine the conversion rate of each type of notification.
Notes:
- Notifications are perishable, meaning a notification disappears exactly after another one is sent.
- A purchase can only be attributed to a notification once.
- A purchase cannot be attributed to a notification if the product has already been purchased at the time of clicking.
- Order the output by
type, alphabetically.
Example:
notifications table
| Column | Type |
|---|---|
| id | INTEGER |
| type | VARCHAR |
| dispatched_at | DATETIME |
| product_id | INTEGER |
notification_events table
| Column | Type |
|---|---|
| notification_id | INTEGER |
| user_id | INTEGER |
| clicked_at | DATETIME |
purchases table
| Column | Type |
|---|---|
| user_id | INTEGER |
| purchased_at | DATETIME |
| product_id | INTEGER |
Output:
| Column | Type |
|---|---|
| notification_type | VARCHAR |
| conversion_rate | FLOAT |
Recommended questions for you
Personalized based on your user activity, skill level, and preferences.
.
.
.
.
.
.
.
.
.
Comments