Handling Missing or Inconsistent Data | Dataford Interview Questions

How do you handle missing or inconsistent data in your reporting?

Using the provided tables, write a SQL query for January 2025 that preserves every campaign, summarizes its available metrics, and assigns a data-quality status based on missing records, null values, invalid negative values, or inconsistent campaign names.

Output

One row per campaign, including campaigns with no January metrics
Columns: campaign_id, campaign_name, channel, total_spend, total_conversions, and data_quality_status
Order by campaign_id ascending

Schema

campaign_directory

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Canonical campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is active

campaign_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric record
campaign_id	INT	Campaign associated with the metric
metric_date	DATE	Date of the reported metric
campaign_name	VARCHAR(100)	Campaign name supplied by the reporting source
spend	NUMERIC(12,2)	Daily campaign spend
conversions	INT	Daily conversion count

Your question is Handling Missing or Inconsistent Data. Start with the requirements and the two 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