Identify Unresolved Monthly Reconciliation Gaps

You have a monthly task to complete and notice that there are gaps in the reconciliation of the data. You are expected to report on the tasks within one week. Tell me what you would do in the period between discovering there was data missing and the task due date?

Using the supplied reconciliation tables, write a query that identifies unresolved gaps for the latest monthly task and provides the information needed for follow-up.

Output

1. One row per unresolved reconciliation item, including missing and rejected submissions.
2. Columns: task_id, month_start, item_id, policy_id, expected_amount, source_amount, submission_status, days_from_discovery_to_due, and prior_month_status.
3. Order by policy_id, then item_id.

Output details

• Treat the latest task as the one with the greatest month_start.
• Use the most recently submitted status per item_id, ordering by submitted_at descending with nulls last, then submission_id descending.
• Interpret rejected submissions as status exactly REJECTED; include items whose latest status is null.
• Calculate source_amount as the sum of transaction amount by policy_id and month_start, leaving it null when no transactions exist.
• Use the preceding month_start status for the same policy_id as prior_month_status.
• Sort policy_id and then item_id in ascending order.