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 |