Answer one question in SQL and in Python, and prove they agree

applied · 50 min · Objective 1.4

Task

Calculate net revenue by product category twice, once in SQL against the database and once in Python against the CSV files, and prove the two independent routes give the same answer.

Steps

  1. In SQL against data/shop.db, join orders to products and sum quantity times unit price times (1 minus discount_pct / 100) by category. Save the result to lab/two-ways/sql.csv with header category,net, rounded to two decimal places and sorted by category.
  2. In Python, using only data/orders.csv and data/products.csv and the csv module, calculate the same thing and save it to lab/two-ways/python.csv in exactly the same format.
  3. Compare the two files.

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/two-ways/sql.csv lab/two-ways/python.csv && echo "SQL and Python agree"
awk -F, 'NR>1 {n++; s+=$2} END {printf "%d categories, total %.2f%s", n, s, ORS}' lab/two-ways/sql.csv

The two files are identical: five categories, totalling 353816.74. When two independent routes agree, the logic is very likely right; when they differ, the difference points straight at the step where one of them went wrong.

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