# Source set dataset stats (Topic 01 — Dataset Stats)

This folder contains the **three “Source set” report packs** used in `Topic 01 (Dataset Stats)`.  
They operate **only** on the `source` dataset (the *search result packs*), and export **36 CSV reports** into:

- `reports/01_dataset_stats/source/`

## What the “source” dataset is

The `source` dataset is the collection of **search-result JSON packs** (loaded into DuckDB as the `source` table). In this project, it consists of:

- **13 hearing-schedule packs** (each contains a `hearings[]` array)
- **1 case-search pack**: `JUN_2025__cr_cases__raissa_carpenter.json` (contains a `cases[]` array)

These “source” packs are **not** the PDF-derived parent/child datasets. They are the *front-door* search results (calendar listings + a case search listing).

## Files in this folder (3-part Source reporting)

You should have these six files here:

1) **Part 1 — Basic source pack stats**
- `01_source__views.sql`
- `01_source__exports.sql`

2) **Part 2 — Hearing datetime/date statistics + conflict detection** *(hearings-only; excludes the case-search pack by construction)*
- `01_source__hearing_datetime_stats__views.sql`
- `01_source__hearing_datetime_stats__exports.sql`

3) **Part 3 — Case-search statistics for the Raissa Carpenter pack**
- `01_source__case_search_stats__views.sql`
- `01_source__case_search_stats__exports.sql`

## Dependencies (must be loaded first)

These scripts assume your “core” layer has already created/loaded:

- the `source` table (NDJSON loaded via your bootstrap pipeline), and
- the shared helper/provenance views.

From the DuckDB CLI, run:

```sql
.read duckdb/sql/core/bootstrap.sql
.read duckdb/sql/core/starter_views__v3_provenance.sql
```

## How to run (DuckDB CLI)

From your project root (so paths resolve cleanly):

```sql
-- Part 1
.read duckdb/sql/topics/01_dataset_stats/source/01_source__views.sql
.read duckdb/sql/topics/01_dataset_stats/source/01_source__exports.sql

-- Part 2
.read duckdb/sql/topics/01_dataset_stats/source/01_source__hearing_datetime_stats__views.sql
.read duckdb/sql/topics/01_dataset_stats/source/01_source__hearing_datetime_stats__exports.sql

-- Part 3
.read duckdb/sql/topics/01_dataset_stats/source/01_source__case_search_stats__views.sql
.read duckdb/sql/topics/01_dataset_stats/source/01_source__case_search_stats__exports.sql
```

### Ensure the reports directory exists

The export scripts write CSVs into:

- `reports/01_dataset_stats/source/`

Create it once (in your shell):

```bash
mkdir -p reports/01_dataset_stats/source
```

(If you prefer, DuckDB CLI usually supports `.shell mkdir -p reports/01_dataset_stats/source`.)

### CSV header option note

DuckDB’s `COPY ... TO ... (FORMAT CSV, HEADER)` is the supported way to include a header row.  
If you ever see an error like “Unrecognized option WITH_HEADER”, replace `WITH_HEADER 1` with `HEADER`.

---

# Provenance and “what to look at first”

## Common provenance columns

Most reports include (or can be joined back to) the same “explain it to a human” identifiers:

- `source_set_id` — derived from the source pack filename (your canonical “which search pack?” key)
- `case_set_id` — e.g. `MCRO` (or other set tags if present)
- `search_category`, `search_type` — describes what the search was (hearing schedule vs case search)
- `search_locations_raw` — raw location string(s) used in the search (when available)
- `case_id` — the familiar case number (recommended “human-facing” key)
- `cluster_id`, `cluster_name` — normalized defendant identity lens (your “truth filter” for de-duplication)

## “WTF” entry points

If you want the quickest “this cannot be real” checks, start with:

- Part 2: **hearing slot collisions** (same datetime + location + judge, huge case counts)
- Part 2: **jury-trial collisions** (many jury trials at the same time/location)
- Part 3: **charges / statutes by disposition** (case-search pack rollups)

---

# Part 1 — Basic Source pack stats (10 CSV reports)

Generated by:
- `01_source__views.sql` (defines views)
- `01_source__exports.sql` (exports to CSV)

### Outputs (written to `reports/01_dataset_stats/source/`)

1. **`source__cases_by_set.csv`**  
   Case-search pack summary by `source_set_id` (case counts, cluster counts, date_filed range).

2. **`source__hearings_by_set.csv`**  
   Hearing-pack summary by `source_set_id` (hearing row counts, unique cases, unique clusters, cancelled count, date range).

3. **`source__cases_by_case_type.csv`**  
   Case-search cases grouped by `case_type` (counts and basic rollups).

4. **`source__hearings_by_case_type.csv`**  
   Hearing rows grouped by `case_type` (how hearing volume distributes across case types).

5. **`source__clusters.csv`**  
   Cluster rollup across hearing packs (cluster_id/name → unique cases, hearing rows, date span).

6. **`source__clusters_top.csv`**  
   Top clusters by hearing volume (a “largest clusters” leaderboard).

7. **`source__judicial_officers.csv`**  
   Judicial-officer rollups (counts, unique clusters/cases, date spans).

8. **`source__attorneys.csv`**  
   Attorney rollups from the hearing packs (counts + distinct cases/clusters where available).

9. **`source__hearing_types.csv`**  
   Hearing types distribution (what kinds of hearings appear, and how often).

10. **`source__hearing_results.csv`**  
    Hearing result distribution (cancelled/continued/etc patterns, depending on your source schema).

---

# Part 2 — Hearing datetime/date stats + conflicts (11 CSV reports)

Generated by:
- `01_source__hearing_datetime_stats__views.sql`
- `01_source__hearing_datetime_stats__exports.sql`

These reports are designed to surface “calendar impossibilities”:
- extremely high counts of cases on the same day,
- extreme peaks in a single location,
- double-booked judges,
- multiple jury trials at the same time/place, etc.

### Outputs

1. **`hearing_dt__date_stats.csv`**  
   Per-hearing-date rollup: number of hearings, unique cases, unique clusters, etc.

2. **`hearing_dt__date_stats_by_location.csv`**  
   Per-date *and* location rollup (find days where one courthouse/location is overloaded).

3. **`hearing_dt__date_stats_by_location_judge.csv`**  
   Per-date + location + judge rollup (the fastest way to find “100+ cases before one officer”).

4. **`hearing_dt__datetime_slot_stats.csv`**  
   Rollups by exact `hearing_datetime` (peaks by exact time slot).

5. **`hearing_dt__slot_by_location.csv`**  
   Rollups by `hearing_datetime` + location (time-slot overload per location).

6. **`hearing_dt__slot_by_location_judge.csv`**  
   Rollups by `hearing_datetime` + location + judge (the “double booking / impossible docket” detector).

7. **`hearing_dt__conflicts_judge_double_booked.csv`**  
   Judge appears in *multiple locations* (or multiple conflicting slots) at the same datetime.

8. **`hearing_dt__conflicts_location_overbooked.csv`**  
   Same datetime + location has unusually high concurrent hearings (configurable thresholds in SQL).

9. **`hearing_dt__conflicts_jury_trials.csv`**  
   Jury trials colliding in the same datetime/location (the “30 jury trials at 9:00 AM” style output).

10. **`hearing_dt__date_location_peaks.csv`**  
    Highlights the most extreme date+location combinations by volume (a “top peaks” leaderboard).

11. **`hearing_dt__datetime_peaks.csv`**  
    Highlights the most extreme datetime slots by volume (a “top peaks by exact slot” leaderboard).

---

# Part 3 — Raissa Carpenter case-search stats (15 CSV reports)

Generated by:
- `01_source__case_search_stats__views.sql`
- `01_source__case_search_stats__exports.sql`

This pack analyzes the single `cases[]`-based source file:
- `JUN_2025__cr_cases__raissa_carpenter.json`

It includes **case-level summaries** and **charge-level exploded reports**.

### Outputs — case-level

1. **`case_search__overview.csv`**  
   Overall counts: cases, unique clusters, date_filed span, active-warrant counts, etc.

2. **`case_search__status_counts.csv`**  
   Cases grouped by `case_status`.

3. **`case_search__location_counts.csv`**  
   Cases grouped by `case_location`.

4. **`case_search__judicial_officer_counts.csv`**  
   Cases grouped by `case_judicial_officer_norm` / officer id.

5. **`case_search__date_filed_histogram.csv`**  
   Date-filed distribution (by day or month, depending on the SQL view).

6. **`case_search__clusters.csv`**  
   Cluster rollup (cluster_id/name → how many cases, date span, etc).

### Outputs — charge-level (exploded from `criminal_charges`)

These come from a normalized “charge rows” view that converts:
`charge_1_*`, `charge_2_*`, ... into one row per charge.

7. **`case_search__charges_per_case.csv`**  
   Distribution of number of charges per case.

8. **`case_search__charge_names.csv`**  
   Most common charge descriptions.

9. **`case_search__charge_statutes.csv`**  
   Most common statutes (e.g. `609.52.2(a)(1)`).

10. **`case_search__charge_dispositions.csv`**  
    Disposition distribution (Dismissed, Convicted, etc — as present in the source).

11. **`case_search__charge_levels.csv`**  
    Charge level distribution (Felony / Gross Misdemeanor / Misdemeanor, etc).

12. **`case_search__statute_by_disposition.csv`**  
    Statute × disposition cross-tab style output (what statutes end up in what outcomes).

### Outputs — convenience/QA reports

13. **`case_search__cases_with_warrants.csv`**  
    Only cases where `case_active_warrant = true`.

14. **`case_search__top_statutes.csv`**  
    “Leaderboard” style: top statutes with counts.

15. **`case_search__top_charge_names.csv`**  
    “Leaderboard” style: top charge descriptions with counts.

---

# Notes on extending

- If you add new `source` packs later, **Part 1 + Part 2** will automatically pick them up (as long as they follow the same schema).  
- Part 3 is intentionally **hard-scoped** to the Raissa Carpenter case-search file via filename match. If you add more case-search packs, you can either:
  - clone Part 3 scripts per pack, or
  - generalize Part 3 to “all packs that contain `cases[]`”.