Summarize valid imports after deduplication
Your task
Keep the highest-import_id row per nonblank trimmed reference. Then discard rows with an unrecognized normalized status or amounts that are NULL, blank, or not all digits after trimming. Return clean_status, valid_orders, and total_cents for the valid rows, sorted by status.
Validate the latest copy. Filtering earlier copies first could incorrectly revive a row superseded by a later invalid one.
Sample data
staged_orders| import_id | order_ref | status_text | amount_text |
|---|---|---|---|
| 1 | 201 | Completed | 2500 |
| 2 | 202 | COMPLETED | 1800 |
| 3 | 203 | completed | 3200 |
| 4 | 203 | completed | 3100 |
| 5 | 204 | REFUNDED | 5000 |
| 6 | 205 | completed | N/A |
| 7 | 206 | completed | 4000 |
| 8 | 207 | cancelled | 2400 |
| 9 | Completed | 1700 | |
| 10 | 209 | unknown | 2900 |
Preview of 10 of 12 rows
Expected output
| clean_status | valid_orders | total_cents |
|---|---|---|
| cancelled | 1 | 2400 |
| completed | 4 | 11400 |
| refunded | 1 | 5000 |
Show a hint
Use ROW_NUMBER per trimmed reference before validation. Cast the trimmed amount only for valid rows.
Data quality and reconciliation guide
Keep raw values available while you clean them
The staged_orders table is an import, so references, statuses, and amounts are text and may contain missing or malformed values. Trim whitespace before testing a field. An empty string after trimming can be turned into NULL to give missing data one representation.
SELECT import_id, NULLIF(TRIM(order_ref), '') AS clean_ref
FROM staged_orders
ORDER BY import_id;Compare records at the same grain
The payment ledger may have several charges or refunds for one order. Aggregate that ledger to one row per order before comparing it with shop_orders. Keep unmatched keys visible so a reconciliation reveals missing orders and missing payments.
SELECT order_id,
SUM(CASE WHEN kind = 'charge' THEN amount_cents ELSE -amount_cents END) AS net_cents
FROM payment_ledger
GROUP BY order_id
ORDER BY order_id;Worked solution
WITH latest AS (SELECT LOWER(TRIM(status_text)) AS clean_status,TRIM(amount_text) AS amount,ROW_NUMBER() OVER (PARTITION BY TRIM(order_ref) ORDER BY import_id DESC) AS pos FROM staged_orders WHERE NULLIF(TRIM(order_ref),'') IS NOT NULL) SELECT clean_status,COUNT(*) AS valid_orders,SUM(CAST(amount AS INTEGER)) AS total_cents FROM latest WHERE pos=1 AND clean_status IN ('completed','refunded','cancelled') AND amount<>'' AND amount NOT GLOB '*[^0-9]*' GROUP BY clean_status ORDER BY clean_status;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
