Email Engagement
ASKED IN INTERVIEWHub Spot
Problem Statement
Calculate the open rate for all HubSpot email campaigns.
Rules:
- Output columns: campaign_id, open_rate.
- open_rate = (Total unique opens / Total unique sends) * 100.
- Round results to 2 decimal places.
- Sort by open_rate in descending order.
Tests your understanding of
Aggregation, Case Logic and Mathematical Operations
Input Tables
campaigns
| campaign_id(INTEGER) | campaign_name(VARCHAR) |
|---|---|
| 1 | New Year Sale |
| 2 | Product Launch |
| 3 | Follow-up |
email_events
| event_id(INTEGER) | campaign_id(INTEGER) | user_id(INTEGER) | event_type(VARCHAR) |
|---|---|---|---|
| 1 | 1 | 101 | send |
| 2 | 1 | 102 | send |
| 3 | 1 | 101 | open |
| 4 | 2 | 103 | send |
| 5 | 2 | 104 | send |
| 6 | 2 | 103 | open |
| 7 | 2 | 104 | open |
| 8 | 3 | 105 | send |
users
| user_id(INTEGER) | email(VARCHAR) |
|---|---|
| 101 | [email protected] |
| 102 | [email protected] |
| 103 | [email protected] |
| 104 | [email protected] |
| 105 | [email protected] |
Expected Output
| campaign_id(INTEGER) | open_rate(DECIMAL) |
|---|---|
| 2 | 100 |
| 1 | 50 |
| 3 | 0 |
Tags
MediumASKED IN INTERVIEWAggregationCase LogicMathematical Operations
15-20 min
51%