VetsLearn Intermediate 03: Data fundamentals
Practice files, revision 2026-10-09

Everything here is made up for practice. No row describes a real person.
The work runs on your own computer. No account, paid service or network
call is needed.

THE FILES

people.csv         One row per person, or it should be. 10 data rows.
                   Columns: person_id, name, branch.
applications.csv   One row per application. 12 data rows.
                   Columns: app_id, person_id, role, submitted_on.
                   person_id names the person who owns the application.
starter.py         Loads both files into SQLite and prints four figures.
                   Two of the four are wrong on purpose. Start here.
expected.json      The answers. Open after your own attempt.
solve.sql          Reference queries. Open after your own attempt.
solve.py           Reference solution. Open after your own attempt.

WHAT IS PLANTED

One person has two applications. One person has three. One person has none.
Three problems are in the data on purpose:
  1. one person_id is on two rows of people.csv
  2. one application has a date that is not a calendar date
  3. one application names a person_id that is not in people.csv
Finding which rows, and deciding what to do with each, is the task.

HOW TO RUN

You need Python 3.8 or newer. Nothing else.

  python starter.py          your starting point; fix its queries
  python solve.py --check    confirms the reference matches expected.json
  python solve.py --sql      runs every query in solve.sql and prints the rows
  python solve.py --pandas   optional; needs pandas installed

If you have the sqlite3 command-line shell (3.32 or newer) you can also run:

  sqlite3 :memory: ".import --csv people.csv people" ".import --csv applications.csv applications" ".read solve.sql"

No Python and no sqlite3? Trace the join on paper from the two CSV files.
Label your result "not executed".

RULES FOR THE TASK

  - Say what one row represents before you count it.
  - Do not edit or delete rows in the CSV files. Clean in a query or a copy.
  - Do not guess a missing or invalid value.
  - Bind values into a query with ? placeholders. Do not build the SQL text
    by joining strings.
  - For each number you report, note the file, the filter and the join that
    produced it.

WHAT THIS DOES NOT SHOW

Twelve made-up rows teach the operations. They say nothing about any real
group of people, and passing these checks does not show that a query is
correct on other data.