Welcome to the SQL screen.
The question is on your right: Pivot Lucid Campaign Performance by Month. Read through the requirements and the three 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 given campaign delivery and conversion data from Lucid Motors marketing efforts. Write a PostgreSQL query that returns one row per campaign for January 2024, showing the campaign name, channel, total spend, total leads, total test-drive bookings, and separate monthly session totals for the Lucid website and the Lucid app. Only include campaigns whose January spend is greater than 0 and whose total January leads are at least 20.
This tests the same thinking behind nested IF statements, INDEX-MATCH lookups, and pivot tables in Excel, but in SQL: conditional logic with CASE, joining lookup data, and reshaping metrics into columns.
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Unique campaign identifier |
| campaign_name | VARCHAR(100) | Campaign name |
| channel | VARCHAR(50) | Marketing acquisition channel |
| region | VARCHAR(50) | Campaign target region |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INT | Unique daily metric row identifier |
| campaign_id | INT | Campaign identifier linked to campaigns |
| metric_date | DATE | Date of the metric |
| surface | VARCHAR(50) | Lucid touchpoint surface such as Website or App |
| spend_usd | NUMERIC(10,2) | Daily media spend in USD |
| sessions | INT | Daily sessions or visits |
| leads | INT | Daily lead count |
| test_drive_bookings | INT | Daily test-drive bookings |
| Column | Type | Description |
|---|---|---|
| channelPK | VARCHAR(50) | Marketing channel |
| target_cpl | NUMERIC(10,2) | Target cost per lead for the channel |
| active_flag | BOOLEAN | Whether the target is currently active |