Reconciling Conflicting KPI Definitions

You are asked to explain how you would reconcile two SQL reports that show different values for the same KPI. Focus on a practical analyst workflow: compare definitions, inspect joins and filters, align grain, test CASE WHEN logic, and identify the records driving the variance.

Problem

What This Tests

Ability to compare SQL report definitions, not just totals
Understanding of join behavior and aggregation grain
Use of CTEs to normalize competing metric logic
Ability to explain root-cause analysis clearly

Typical Mismatch Signals

Different row counts after joins

Different date columns or month boundaries

Different status filters

Different `CASE WHEN` inclusion rules

Duplicate rows introduced before aggregation

Problem

What This Tests

Ability to compare SQL report definitions, not just totals
Understanding of join behavior and aggregation grain
Use of CTEs to normalize competing metric logic
Ability to explain root-cause analysis clearly

Typical Mismatch Signals

Different row counts after joins

Different date columns or month boundaries

Different status filters

Different `CASE WHEN` inclusion rules

Duplicate rows introduced before aggregation

Problem

What This Tests

Ability to compare SQL report definitions, not just totals
Understanding of join behavior and aggregation grain
Use of CTEs to normalize competing metric logic
Ability to explain root-cause analysis clearly

Typical Mismatch Signals

Different row counts after joins

Different date columns or month boundaries

Different status filters

Different `CASE WHEN` inclusion rules

Duplicate rows introduced before aggregation

Interview Guides

Problem

What This Tests

Typical Mismatch Signals

Problem

What This Tests

Typical Mismatch Signals

Reconciling Conflicting KPI Definitions

Problem

What This Tests

Typical Mismatch Signals

Problem

What This Tests

Typical Mismatch Signals