Count the rows a join adds and the rows it loses

short · 40 min · Objective 2.1

Task

Join the orders to the customers two ways: to the clean customer table on its key, and to the raw customer file on email address. Count the rows each join returns and account for every difference.

Steps

  1. Inner join orders to customers on customer_id in data/shop.db and count the rows.
  2. Load data/customers_raw.csv into a table raw in a copy of the database, lab/joins/work.db. Join orders to customers, then to raw on lower(raw.email) = lower(customers.email), and count the rows.
  3. Count the orders whose customer's email is blank in the raw file, and the orders belonging to customers who appear twice in the raw file.
  4. Record the four counts in lab/joins/counts.txt as key join: N, email join: N, lost to blank emails: N and added by duplicates: N.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

grep -E '^(key join|email join|lost to blank emails|added by duplicates): ' lab/joins/counts.txt
awk -F': ' '/^key/ {k=$2} /^email/ {e=$2} /^lost/ {l=$2} /^added/ {a=$2} END {print (k-l+a==e) ? "the bridge closes" : "the bridge does not close"}' lab/joins/counts.txt

The key join returns 2000 rows. The email join returns 1953: 126 orders lost because their customers' emails are blank, and 79 added because 14 customers appear twice. 2000 minus 126 plus 79 is 1953, so the bridge closes -- a difference of 47 rows hiding two errors totalling 205.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.