Chapter 4: Checking an answer

1 An oracle that the register confirms

Count Pirckheimer's correspondents and letters between 1520 and 1530 and check both figures against the register.

Partitur

---
id: count-checked
display: kennzahl
oracle:
  n_correspondents: 77
  n_letters: {min: 350}
duckdb:
  - alias: main
    path: $PDAI_COLLECTIONS/pirckheimer/pirckheimer.duckdb
    read_only: true
partiturs:
  - id: duckdb-read
    input:
      sql: |
        WITH l AS (
          SELECT CASE WHEN sender_id = 'ent_pirckheimer'
                      THEN recipient_id ELSE sender_id END AS partner,
                 TRY_CAST(left(date_when, 4) AS INTEGER) AS year
          FROM letters_resolved
        )
        SELECT count(DISTINCT partner) AS n_correspondents,
               count(*) AS n_letters
        FROM l
        WHERE year BETWEEN 1520 AND 1530
artefacts:
  - name: count
    kind: table
    file: count.json
---
Count Pirckheimer's correspondents and letters between 1520 and 1530 and check
both figures against the register.

77 Briefpartner, 354 Briefe.

bestanden

77.0Briefpartner erwartet = 77

354.0Briefe erwartet mindestens 350

2 Every partner id, including the two placeholders

Count every partner id between 1520 and 1530, including the register's placeholder ids for unnamed correspondents.

Partitur

---
id: count-golden
display: kennzahl
oracle:
  n_correspondents: 77
duckdb:
  - alias: main
    path: $PDAI_COLLECTIONS/pirckheimer/pirckheimer.duckdb
    read_only: true
partiturs:
  - id: duckdb-read
    input:
      sql: |
        WITH l AS (
          SELECT CASE WHEN sender_id = 'ent_pirckheimer'
                      THEN recipient_id ELSE sender_id END AS partner,
                 TRY_CAST(left(date_when, 4) AS INTEGER) AS year
          FROM letters_resolved
        )
        SELECT count(DISTINCT partner) AS n_correspondents
        FROM l
        WHERE year BETWEEN 1520 AND 1530
artefacts:
  - name: count
    kind: table
    file: count.json
---
Count every partner id between 1520 and 1530, including the register's
placeholder ids for unnamed correspondents.

77 Briefpartner.

bestanden

77.0Briefpartner erwartet = 77

3 Where the two come from

The list of Chapter 1 contains a partner called “(unbekannt)”. The register keeps two placeholder ids for letters whose correspondent the edition does not name. This block counts again without them.

Count the named correspondents between 1520 and 1530, leaving out the register's placeholder ids for unnamed correspondents.

Partitur

---
id: count-named
display: kennzahl
golden: golden-c782fab71c
oracle:
  n_correspondents: 75
duckdb:
  - alias: main
    path: $PDAI_COLLECTIONS/pirckheimer/pirckheimer.duckdb
    read_only: true
partiturs:
  - id: duckdb-read
    input:
      sql: |
        WITH l AS (
          SELECT CASE WHEN sender_id = 'ent_pirckheimer'
                      THEN recipient_id ELSE sender_id END AS partner,
                 TRY_CAST(left(date_when, 4) AS INTEGER) AS year
          FROM letters_resolved
        )
        SELECT count(DISTINCT partner) AS n_correspondents,
               count(*) AS n_letters
        FROM l
        WHERE year BETWEEN 1520 AND 1530
          AND partner NOT LIKE 'ent_unbekannt%'
artefacts:
  - name: count
    kind: table
    file: count.json
---
Count the named correspondents between 1520 and 1530, leaving out the
register's placeholder ids for unnamed correspondents.

75 Briefpartner, 334 Briefe.

bestanden

75.0Briefpartner erwartet = 75

334.0Briefe

4 Which correspondents share Pirckheimer’s surname?

Which correspondents carry an entity id that begins with Pirckheimer's own?

Partitur

---
id: family
display: tabelle
duckdb:
  - alias: main
    path: $PDAI_COLLECTIONS/pirckheimer/pirckheimer.duckdb
    read_only: true
partiturs:
  - id: duckdb-read
    input:
      sql: |
        WITH l AS (
          SELECT CASE WHEN sender_id = 'ent_pirckheimer'
                      THEN recipient_id ELSE sender_id END AS partner
          FROM letters_resolved
        )
        SELECT e.canonical AS name, l.partner AS ent_id, count(*) AS count
        FROM l JOIN ent_canonical AS e ON e.ent_id = l.partner
        WHERE l.partner LIKE 'ent_pirckheimer_%'
        GROUP BY e.canonical, l.partner
        ORDER BY count(*) DESC
artefacts:
  - name: family
    kind: table
    file: family.json
---
Which correspondents carry an entity id that begins with Pirckheimer's own?

ohne Erwartung

Pirckheimer, Klara20.0
Pirckheimer, Sabina10.0
Pirckheimer, Katharina d. J.9.0
Pirckheimer, Caritas d. J.6.0
Pirckheimer, Eufemia2.0