Problem
Write a PostgreSQL query to reconcile invoice totals between Literati billing and ERP data for January 2024. Return one row per billing invoice, use the latest ERP snapshot per invoice_number, classify each invoice as match, amount_mismatch, or missing_in_erp, and show the absolute difference when both amounts exist.
Schema
| Column | Type | Description |
|---|---|---|
| invoice_id | ||
| invoice_number | ||
| member_id | ||
| invoice_date | ||
| billed_amount |
| Column | Type | Description |
|---|---|---|
| snapshot_id | ||
| invoice_number | ||
| snapshot_date | ||
| erp_amount |
Practicing as: Financial Analyst interview at LiteratiHi, I'll play your Literati interviewer for the Financial Analyst role. Candidates describe these interviews as often stressful and moderately difficult, so expect me to be direct and to the point. Take your time with the question above and answer like we're in the room.
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
