# ClickHouse funnel and retention SQL verification

These examples use synthetic events only. They verify SQL semantics; they do not test SensorFlow SDK ingestion, database throughput, or a production deployment.

## Reproduce

Download all files in this directory, install the official ClickHouse binary, then run:

```sh
python3 verify.py --out verified-results.json
```

The verifier runs each query with `clickhouse local` in a temporary directory and checks exact output. It needs no ClickHouse server, credentials, or network connection. The recorded run used ClickHouse 25.9.2.1 on macOS. A successful run prints five `PASS` lines and exits with status 0. The JSON report records the version, platform, query hashes, expected output, and actual output.

## Files

- `funnel.sql`: three-step ProductView -> AddToCart -> Purchase funnel and expected levels for three users.
- `funnel-edge-cases.sql`: window boundary, repeated events, input order, and wrong step order.
- `funnel-strict.sql`: `strict_increase` behavior when steps share a timestamp.
- `retention.sql`: D1 and D7 natural-day cohort retention.
- `retention-edge-cases.sql`: duplicate activity and an immature D7 cohort, represented as NULL.

The examples do not prescribe business identity stitching, time zone, late-arrival policy, or event deduplication. Define those before using a production query. `verified-results.json` is a record of one local run, not a guarantee across all ClickHouse versions.