SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 230 of 250

Easy

Normalize statuses from an import

Your task

Return import_id and clean_status for every staged row. Strip surrounding spaces and convert the status to lowercase. Sort by import ID.

Trim and lowercase a status before grouping or comparing it.

Sample data

Table:staged_orders
import_idorder_refstatus_textamount_text
1 201 Completed 2500
2202COMPLETED1800
3203completed3200
4 203 completed 3100
5204REFUNDED5000
6205completedN/A
7206 completed 4000
8207cancelled2400
9Completed1700
10209unknown2900

Preview of 10 of 12 rows

Expected output

import_idclean_status
1completed
2completed
3completed
4completed
5refunded
6completed
7completed
8cancelled
9completed
10unknown
11completed
12completed
Show a hint

Apply LOWER(TRIM(status_text)).

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;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…