# Advanced SQL lab

Original synthetic data for windows, cohorts, sessions, history and query-planning lessons. No actual customers or business outcomes are represented. All monetary amounts are integer paise.

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

Python standard library only; queries run in SQLite memory. To explore from this directory: `from build_and_verify import database; db=database()`. The script writes CSVs, schema and actual reference-check outputs. Article examples may add self-contained CTE fixtures without altering these tables.

## Tables and contracts

- `purchases`: one completed purchase; 11 rows, five customer IDs and 135,000 paise across January–April 2026. Acquisition is first observed purchase, not verified first-ever purchase. The example observation cutoff is 1 May 2026.
- `events`: one event ID. Event time describes behaviour; ingestion time describes arrival. C's view arrives after its later purchase event, deliberately demonstrating late arrival. One fixed hypothetical timezone is used.
- `daily_activity`: one customer/date of observed activity; suitable for consecutive-day grouping. Absence represents inactivity only under the exercise's completeness assumption.
- `staff`: a small acyclic reporting hierarchy; `manager_id` is NULL for the root.
- `customer_history`: region versions with inclusive valid_from and exclusive valid_to. NULL valid_to means open-ended. A moves from North to West on 1 March.
- `deliveries`: eight synthetic delivery durations in minutes, including a long tail. These are not service-level benchmarks.
- `calendar`: one date per day from 1 January through 30 April 2026. Calendar completeness does not prove event-source completeness.

Reference results: monthly purchase values 25,000 / 28,000 / 52,000 / 30,000 paise; acquisition cohort sizes 2 / 1 / 1 / 1; delivery median 27.5 minutes. Historical-region attribution differs from attributing all transactions to a customer's current region.

Small-data execution verifies calculations, not production performance. SQLite version details appear in the results file. No external service or credentials are needed. Original data/code may be used, modified and redistributed for learning with attribution to NeuraPath Academy.

## Operational examples

Run `python operational_checks.py` from this directory for query-plan comparisons,
parameter-binding checks and a two-batch ingestion/replay demonstration. Results
are written to `operational-results.json`. The script uses in-memory databases;
it does not contact a service or modify a production database. Query plans are
diagnostic and version-dependent. The small fixture does not establish a speedup.