Recompute the headline numbers by a second route

short · 35 min · Objective 4.3

Task

Calculate the same three headline figures twice, once in SQL against the database and once in Python against the CSV files, and prove the two routes agree.

Steps

  1. Route 1: with sqlite3 on data/shop.db, join orders to products and, for orders with quantity above zero, calculate the count, the total quantity and ROUND(SUM(o.quantity * p.unit_price * (1 - o.discount_pct / 100.0)), 2).
  2. Write the three as orders: N, quantity: N and net revenue: N to lab/validate/sql.txt.
  3. Route 2: calculate the same three from data/orders.csv and data/products.csv with the csv module, and write them in the same format to lab/validate/csv.txt.

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.

diff lab/validate/sql.txt lab/validate/csv.txt && echo "routes agree"
cat lab/validate/sql.txt

The routes agree: 1997 orders, 5703 units and 354627.14 of net revenue. Note the 100.0: discount_pct is an INTEGER column, so discount_pct / 100 is integer division, which makes every discount zero and reports the gross 369681.37 instead. A second route catches exactly that kind of error.

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