SQL Window Functions for KPIs | Dataford Interview Questions

Can you write a SQL query with window functions to track campaign KPIs over time?

Use the provided campaign and daily KPI data. Return records for active campaigns only, calculate click-through rate, and assign each campaign's KPI records a chronological day number.

Output

One row per daily KPI record for an active campaign.
Columns: campaign_name, metric_date, impressions, clicks, conversions, spend, ctr_pct, and day_number.
Sort by campaign name, metric date ascending, and KPI ID ascending for ties.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign display name
status	VARCHAR(20)	Campaign lifecycle status

campaign_daily_kpis

Column	Type	Description
kpi_idPK	INT	Unique daily KPI record identifier
campaign_id	INT	Referenced campaign
metric_date	DATE	Date of the KPI measurement
impressions	INT	Number of ad impressions
clicks	INT	Number of ad clicks
conversions	INT	Number of conversions
spend	NUMERIC(12,2)	Daily campaign spend

Your question is SQL Window Functions for KPIs. 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