SQL KPI Pivot Report | Dataford Interview Questions

Your question is SQL KPI Pivot Report. Start with the requirements and the two 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.

Problem

Business Context

Datavant Link operations needs a monthly report showing several linkage KPIs in a pivot-style result, with one row per organization and month.

Task

Write a PostgreSQL query that reports production Datavant Link runs from January 1 through March 31, 2025. Use conditional aggregation to place multiple KPIs in separate columns.

Requirements

Group results by calendar month and organization, labeling missing organizations as Unknown.
Return total runs, completed runs, total input records, matched records, completed-run match rate as a percentage, and average processing time in minutes.
Include failed and pending production runs in total run counts, but calculate match rate and average processing time only from completed runs. Avoid division by zero.
Sort by month ascending and organization name ascending.

Schema

organizations

Column	Type	Description
organization_idPK	INTEGER	Unique organization identifier
organization_name	VARCHAR(100)	Organization display name

linkage_runs

Column	Type	Description
run_idPK	INTEGER	Unique linkage run identifier
organization_id	INTEGER	Organization associated with the run
started_at	TIMESTAMP	Time at which the run started
completed_at	TIMESTAMP	Time at which the run completed
run_status	VARCHAR(20)	Current run status
environment	VARCHAR(20)	Execution environment
input_records	INTEGER	Number of records submitted
matched_records	INTEGER	Number of records successfully matched