Your question is Flag Missing Marketing Spend Days. 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.
You are given a small marketing pipeline feeding an executive dashboard. Write a PostgreSQL query that returns one row per report_date and campaign_name for January 2024, showing the cleaned spend, clicks, and conversions used in reporting, plus a data quality status. Treat missing metric rows as 0, negative spend or clicks as anomalies that should be replaced with 0, and rows with NULL conversions as missing conversions that should also be reported as 0. Label each row as ok, missing_metrics, or anomalous_metrics.
Return only active Google Ads campaigns, keep dates even when no metric row exists, and sort by date then campaign name.
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Primary key for the campaign |
| campaign_name | VARCHAR(100) | Google marketing campaign name |
| channel | VARCHAR(50) | Acquisition channel such as Google Ads or DV360 |
| is_active | BOOLEAN | Whether the campaign is currently active |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INT | Primary key for the daily metric row |
| campaign_id | INT | Campaign identifier |
| report_date | DATE | Date of the reported metrics |
| spend_usd | NUMERIC(10,2) | Daily ad spend in USD |
| clicks | INT | Daily clicks |
| conversions | INT | Daily conversions |
| Column | Type | Description |
|---|---|---|
| calendar_datePK | DATE | Calendar date used to generate expected reporting days |