Your question is SQL Joins for Campaign and Transactions. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a SQL query utilizing different types of JOINs to combine Seatgeek marketing campaign spend data with user transaction records.
Use the provided tables and return one row per campaign with spend. Include campaigns with no matching completed transactions, exclude spend rows whose campaign ID is not defined, and count only completed transactions.
campaign_id, campaign_name, total_spend, completed_transactions, completed_revenuecampaign_id ascending| Column | Type | Description |
|---|---|---|
| campaign_idPK | VARCHAR(20) | Unique Seatgeek marketing campaign identifier |
| campaign_name | VARCHAR(100) | Campaign display name |
| channel | VARCHAR(30) | Primary marketing channel |
| Column | Type | Description |
|---|---|---|
| spend_idPK | INT | Unique spend record identifier |
| campaign_id | VARCHAR(20) | Campaign associated with the spend record |
| spend_date | DATE | Date the spend was recorded |
| spend_amount | NUMERIC(12,2) | Marketing spend amount |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique user transaction identifier |
| user_id | INT | User associated with the transaction |
| campaign_id | VARCHAR(20) | Campaign attributed to the transaction |
| transaction_date | DATE | Date of the transaction |
| status | VARCHAR(20) | Transaction lifecycle status |
| gross_amount | NUMERIC(12,2) | Gross transaction amount |