Flag Missing Marketing Spend Days | Dataford Interview Questions

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.

Schema

campaigns

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

daily_metrics

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

calendar

Column	Type	Description
calendar_datePK	DATE	Calendar date used to generate expected reporting days

Your question is Flag Missing Marketing Spend Days. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem