SQL KPI Pivot Report | Dataford Interview Questions

SQL KPI Pivot Report

MediumSQL · PostgreSQL

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

Tablesorganizationslinkage_runs