Business Context
You’re on the data engineering team at a fintech payments processor that settles card and bank-transfer transactions for thousands of merchants. Operations agents can create manual_entries to correct partner file issues (e.g., missing references, late-arriving settlement files). Because these entries can impact reconciliation and regulatory reporting, the risk team runs a daily audit to detect data integrity problems.
In the last week, the audit dashboard showed a spike in duplicate partner references and broken links to the canonical transactions table. Your task is to generate a daily integrity report for the last 7 days of data, anchored to the most recent manual_entries.created_at date in the dataset (not CURRENT_DATE, to keep results deterministic for backfills).
Task
Write a SQL query that produces daily integrity metrics for manual entries in the last 7 days.
Requirements
- Define the reporting window as dates
>= (max(created_at)::date - 6 days) where max(created_at) is taken from manual_entries.
- For each
entry_date (UTC date of manual_entries.created_at) return:
total_entries: number of manual entries created that dayduplicate_partner_refs: number of entries whose (partner_id, partner_ref) appears more than once within the 7-day windowconflicting_amount_or_currency: number of entries that are duplicates and where the duplicate group has more than one distinct amount or more than one distinct currencymissing_linked_txn: number of entries where linked_transaction_id is not null but does not exist in transactions
- Output must be ordered by
entry_date ascending.
Table Definitions
manual_entries
| column | type | description |
|---|
| entry_id | BIGINT | Primary key |
| partner_id | INT | Partner identifier |
| partner_ref | VARCHAR(64) | Reference from partner file / operator entry |
| amount | DECIMAL(12,2) | Entered amount |
| currency | CHAR(3) | ISO currency code |
| linked_transaction_id | BIGINT | Nullable reference to transactions.transaction_id (not always enforced) |
| created_by | VARCHAR(64) | Operator username |
| created_at | TIMESTAMP | Entry creation timestamp (UTC) |
transactions
| column | type | description |
|---|
| transaction_id | BIGINT | Primary key |
| partner_id | INT | Partner identifier |
| amount | DECIMAL(12,2) | Settled amount |
| currency | CHAR(3) | ISO currency code |
| status | VARCHAR(24) | Transaction status (e.g., SETTLED, DECLINED) |
| created_at | TIMESTAMP | Transaction creation timestamp (UTC) |
Sample Data
transactions
| transaction_id | partner_id | amount | currency | status | created_at |
|---|
| 9001 | 10 | 120.00 | USD | SETTLED | 2026-02-10 08:55:00 |
| 9002 | 10 | 75.00 | USD | SETTLED | 2026-02-09 12:10:00 |
| 9100 | 11 | 50.00 | EUR | SETTLED | 2026-02-11 13:00:00 |
manual_entries
| entry_id | partner_id | partner_ref | amount | currency | linked_transaction_id | created_by | created_at |
|---|
| 101 | 10 | A-1001 | 120.00 | USD | 9001 | ops_jane | 2026-02-10 09:15:00 |
| 102 | 10 | A-1001 | 120.00 | USD | 9001 | ops_mike | 2026-02-10 09:18:00 |
| 103 | 10 | A-1002 | 75.00 | USD | 9999 | ops_jane | 2026-02-10 10:05:00 |
| 104 | 11 | B-2001 | 50.00 | EUR | NULL | ops_lee | 2026-02-11 14:20:00 |
| 105 | 11 | B-2001 | 55.00 | EUR | NULL | ops_lee | 2026-02-11 14:22:00 |
Expected Output
| entry_date | total_entries | duplicate_partner_refs | conflicting_amount_or_currency | missing_linked_txn |
|---|
| 2026-02-10 | 3 | 2 | 0 | 1 |
| 2026-02-11 | 2 | 2 | 2 | 0 |
