Flag Discrepant M-Pathways Report Totals | Dataford Interview Questions

The University of Michigan Operations team wants a quick way to spot reporting discrepancies in M-Pathways expense data. Write a SQL query to identify departments where the difference between the reported total and the actual total exceeds a small tolerance.

Requirements

Use only the m_pathways_expense_reports table.
For each department_name, calculate:
- actual_total as the sum of actual_amount
- reported_total as the sum of reported_amount
- discrepancy_amount as reported_total - actual_total
Return only departments where the absolute discrepancy is greater than 50.
Sort the results by discrepancy_amount descending.

Schema

m_pathways_expense_reports

Column	Type	Description
report_idPK	INT	Unique report row identifier
department_name	VARCHAR(100)	University of Michigan department name
report_month	DATE	Month represented by the expense report row
actual_amount	DECIMAL(10,2)	Actual recorded expense amount
reported_amount	DECIMAL(10,2)	Published report expense amount
report_status	VARCHAR(20)	Status of the report row such as published, draft, or pending

Your question is Flag Discrepant M-Pathways Report Totals. Start with the requirements and the one table 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

Requirements

Use only the m_pathways_expense_reports table.
For each department_name, calculate:
- actual_total as the sum of actual_amount
- reported_total as the sum of reported_amount
- discrepancy_amount as reported_total - actual_total
Return only departments where the absolute discrepancy is greater than 50.
Sort the results by discrepancy_amount descending.

Schema

m_pathways_expense_reports

Column	Type	Description
report_idPK	INT	Unique report row identifier
department_name	VARCHAR(100)	University of Michigan department name
report_month	DATE	Month represented by the expense report row
actual_amount	DECIMAL(10,2)	Actual recorded expense amount
reported_amount	DECIMAL(10,2)	Published report expense amount
report_status	VARCHAR(20)	Status of the report row such as published, draft, or pending

Your question is Flag Discrepant M-Pathways Report Totals. Start with the requirements and the one table 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.