Reconcile Dashboard and Source Totals | Dataford Interview Questions

Write a PostgreSQL query to troubleshoot a mismatch between the Synechron dashboard and the source system for daily settled revenue. Return only date-region combinations where the dashboard total differs from the correctly derived source total, and include prior-day difference context for the same region.

Schema

Tables

source_transactions
accounts
synechron_dashboard_daily

What This Tests

Filtering source rows to the metric definition
Joining transactions to account attributes
Aggregating both systems to the same grain
Using FULL OUTER JOIN for reconciliation
Using LAG to add debugging context

Your question is Reconcile Dashboard and Source Totals. Start with the requirements and the three 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

Schema

Tables

source_transactions
accounts
synechron_dashboard_daily

What This Tests

Filtering source rows to the metric definition
Joining transactions to account attributes
Aggregating both systems to the same grain
Using FULL OUTER JOIN for reconciliation
Using LAG to add debugging context

Your question is Reconcile Dashboard and Source Totals. Start with the requirements and the three 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.