Welcome to the SQL screen.
The question is on your right: Unify Campaign Performance Sources. Read through the requirements and the four tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
You are asked to build a unified monthly campaign report by combining lead data from Salesforce, email engagement from Marketo, and web sessions from Google Analytics. Write a PostgreSQL query that returns one row per campaign for January 2024, showing the campaign name, total leads created, number of MQLs, total email sends, total email clicks, total web sessions, and a derived campaign_status that is Healthy when the campaign has at least 2 MQLs and a click-through rate of at least 10%; otherwise mark it Needs Review. Include campaigns even if they are missing data from one of the sources.
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Unique campaign identifier |
| campaign_name | VARCHAR(100) | Campaign name |
| Column | Type | Description |
|---|---|---|
| lead_idPK | INT | Unique lead identifier |
| campaign_id | INT | Associated campaign identifier |
| created_at | DATE | Lead creation date |
| lead_status | VARCHAR(20) | Lead lifecycle status |
| Column | Type | Description |
|---|---|---|
| stat_idPK | INT | Unique email stat record identifier |
| campaign_id | INT | Associated campaign identifier |
| activity_date | DATE | Email activity date |
| sends | INT | Email sends for the record |
| clicks | INT | Email clicks for the record |
| Column | Type | Description |
|---|---|---|
| session_idPK | INT | Unique session record identifier |
| campaign_id | INT | Associated campaign identifier |
| session_date | DATE | Web session date |
| sessions | INT | Number of sessions |