# Commerce SQL lab — original synthetic data

This small, invented dataset is designed for inspection and calculation, not as a sample of real customer behaviour. All emails use the reserved `.test` domain. No customer, employer or student records are included.

Run from the repository root with Python 3.10 or later:

```powershell
python 11-Blog-Programme/labs/commerce-sql/build_and_verify.py
```

Only Python's standard library is needed. The script writes the five CSV files, schema and actual verification results. Queries run in an in-memory SQLite database. To explore interactively, start Python in this lab directory, then use `from build_and_verify import database; db = database()` and `db.execute("SELECT ...").fetchall()`.

## Data contract

| Table | One row represents | Key / deliberate issue |
|---|---|---|
| customers | A known customer | customer_id; C007 has no orders; two emails are missing |
| orders | An order header | order_id; O1009 refers to missing customer C999 |
| order_items | An item line on an order | line_id; order_id can repeat |
| refunds | A refund event | refund_id; O1005 has two partial refunds |
| payment_events | An ingested payment event | event_id is deliberately repeated for P02 |

Amounts are integer paise; 100 paise equals one rupee. `order_total_paise` is the recorded final order amount before refunds. `line_total_paise` already includes the line's allocated discount and reconciles to the order header. Do not subtract `discount_paise` again. That field is descriptive, and NULL means the discount amount was not recorded, not necessarily zero. Empty CSV cells represent SQL NULL in this fixture.

Timestamps use one fixed, hypothetical business timezone and ISO text format with second precision. There is no timezone conversion in this lab. Only `completed` orders are eligible for the completed-order-value metric. Cancelled and pending orders are excluded. Known-customer matching is not an eligibility requirement: O1009 remains in the total and is separately flagged for investigation.

The expected completed-order value is 104,000 paise across eight orders. This teaching metric is not a statement of statutory revenue recognition, tax treatment or settlement. Refund-adjusted order value is a separate metric: 86,500 paise after four refund events totalling 17,500 paise. Three orders have at least one refund.

## Checks and limitations

The verifier compares 20 SQL outputs with independently specified small reference values. It also alters one order amount and confirms that a reconciliation check detects the changed order. Deliberately wrong queries are retained to demonstrate fan-out, unmatched-record loss and misuse of DISTINCT; their expected wrong results are explicitly labelled.

This fixture demonstrates correctness at inspectable scale. It does not establish performance on large data, accounting compliance, a complete production schema or a security boundary. SQL dialect differences should be checked before running examples on another engine. Primary references: [SQLite SELECT](https://www.sqlite.org/lang_select.html), [aggregates](https://www.sqlite.org/lang_aggfunc.html), [Python sqlite3](https://docs.python.org/3/library/sqlite3.html).

The original synthetic data and code in this lab may be used, modified and redistributed for learning with attribution to NeuraPath Academy. No third-party dataset is repackaged here.