Deliver a Sales Reconciliation

Introduction

PAPER & PIER needs a closing handover: one CSV row per order and a summary for each stall. Both stalls can have a zero overall difference while some individual orders remain underpaid or overpaid. The handover must retain those exceptions rather than announcing success from one grand total.

You will save a reusable SQL query and use the supplied exporter to create real CSV files from its actual results. First complete Keep Each Order Total Correct, or know SUM/GROUP BY, LEFT JOIN, COALESCE and the duplicate-join problem. Common table expressions and status classification are explained here. Use Terminal throughout; this unit has its own independent six-order ledger.

Keep all four source tables and supplied tools unchanged. Change query.sql and files under reports/ only; do not add database objects. All money is integer cents. This is a simplified receipt reconciliation, without taxes or payment-provider settlement. The exporter handles CSV encoding and summary arithmetic; you do not need to write Python.

Save a Reusable Per-Order Query

In this step, you will calculate each order's net receipts, difference and status using one self-contained SQL statement. Begin in Terminal:

cd /home/labex/project/sales-desk

sqlite3 -header -column ledger.db "SELECT * FROM orders ORDER BY order_id;"

This independent ledger has A1 through A6, including two orders labeled Alex Kim and labels containing commas or quote characters. IDs distinguish orders; keep labels exactly as stored. Review every fact list before deriving balances:

sqlite3 -header -column ledger.db "SELECT * FROM items ORDER BY item_id; SELECT * FROM payments ORDER BY payment_id; SELECT * FROM refunds ORDER BY refund_id;"

As before, only succeeded payments and refunds count. Two successful events with equal amounts both count. Missing eligible facts contribute zero. The arithmetic is:

A common table expression (CTE) is a named intermediate SELECT available only inside one statement. WITH name AS (...) introduces it; commas separate multiple CTEs. It provides the same separate-fact reasoning as the earlier views without creating database objects or depending on another lab's files.

First, three CTEs aggregate each fact independently. Then totals starts with orders and fills missing totals with zero. balances calculates net and difference from those named totals. The last SELECT uses CASE WHEN ... THEN ... ELSE ... END to choose the status from the difference's sign. These layers make aliases available to the following SELECT instead of relying on another expression's alias in the same SELECT list. SELECT * in the intermediate balance layer carries forward the totals; the final SELECT explicitly lists exactly nine columns in the required order.

Save the full statement. cat > replaces the file; the quoted here-document preserves SQL literally. Include the final SQL line:

cat > query.sql <<'SQL'
WITH
item_totals AS (
  SELECT order_id, SUM(quantity * unit_cents) AS due_cents
  FROM items GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(amount_cents) AS paid_cents
  FROM payments WHERE status = 'succeeded' GROUP BY order_id
),
refund_totals AS (
  SELECT order_id, SUM(amount_cents) AS refunded_cents
  FROM refunds WHERE status = 'succeeded' GROUP BY order_id
),
totals AS (
  SELECT o.order_id, o.customer, o.stall,
         COALESCE(i.due_cents, 0) AS due_cents,
         COALESCE(p.paid_cents, 0) AS paid_cents,
         COALESCE(r.refunded_cents, 0) AS refunded_cents
  FROM orders AS o
  LEFT JOIN item_totals AS i ON i.order_id = o.order_id
  LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
  LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
),
balances AS (
  SELECT *, paid_cents - refunded_cents AS net_cents,
         paid_cents - refunded_cents - due_cents AS difference_cents
  FROM totals
)
SELECT order_id, customer, stall, due_cents, paid_cents,
       refunded_cents, net_cents, difference_cents,
       CASE WHEN difference_cents = 0 THEN 'settled'
            WHEN difference_cents < 0 THEN 'underpaid'
            ELSE 'overpaid' END AS status
FROM balances
ORDER BY order_id;
SQL

Do not include SQLite dot commands or shell commands in query.sql: the exporter executes one ordinary SELECT/WITH statement. It must work from the four source tables on another ledger with the same schema, including different order IDs and amounts. Hardcoded result rows or database views would not provide that reusable query.

< sends this file into the SQLite CLI for a readable first run:

sqlite3 -header -column ledger.db < query.sql

Read the actual rows, not just a total. Expected balances are:

A2 remains visible without a payment. A3 retains both successful 50-cent refunds. A6's failed refund does not reduce its net receipts. A1 and A2 remain separate despite sharing a customer label. All five cent columns are actual integers, including negative differences; do not turn them into formatted currency strings.

For a syntax error, read the reported location and replace query.sql with the corrected complete block before rerunning. For a wrong path, return using the absolute cd above and use ls to find query.sql. Do not clear the ledger, modify amounts to force a balance, or add stored result tables.

Deliver and Read the Actual CSV Files

In this step, you will export the saved query and read its real handover files, including labels that require CSV quoting. Begin in Terminal in /home/labex/project/sales-desk.

./export is a supplied executable in the current directory. It reads query.sql, executes that unchanged statement against ledger.db using a read-only connection, and writes the actual returned rows to reports/orders.csv. It derives reports/summary.csv from those returned rows only. It does not repair a wrong query or add missing orders. The prepared reports directory is where both files belong:

./export

The message reports six query rows and names both output files. The reader below will show two stall summaries. A successful export means files were written, not that your arithmetic is correct. If the query is missing or invalid, the exporter reports a failure. Old files may still be present from an earlier successful export: fix the saved query and run ./export again before treating them as current.

./review is the supplied CSV reader. It reads the saved files without consulting the database, and displays each parsed row as a labeled record. Missing or altered output stays missing or altered:

./review

The six order records should agree with the previous step's table, with exact original customer and stall labels. The summary columns are:

North, Pier has three orders: due 1650, net 1650, difference 0, and one of each status. South also has three: due 1100, net 1100, difference 0, and one of each status. Across both stalls there are four unsettled orders even though the grand difference is zero. Overpayments and underpayments cancel in a total; they do not settle somebody else's order.

CSV means comma-separated values, but a comma inside a label is part of one field. Standard CSV surrounds such fields with double quotes and represents a quote inside a field with two quote characters. cat displays raw file bytes as text; compare that encoding with the correctly parsed labels from ./review:

cat reports/orders.csv

cat reports/summary.csv

In the order file, the customer label Paper, "Pine" is encoded as "Paper, ""Pine"""; North, Pier is encoded as "North, Pier". These extra quote characters are CSV syntax, not changes to the labels. Splitting every line at each comma would corrupt these records; the supplied writer and reader handle the format.

If a CSV is missing, rerun ./export after fixing query.sql, then read both files again. Do not hand-edit a summary to hide missing orders, replace the exporter/reader, or change source records. Your handover consists of the reusable query.sql plus both real CSV files. The files should contain one row for every source order, the exact nine order columns, and the eight summary columns shown above. Row order is not the business result; identities, values, labels and their relationships are.

Summary

You built a reusable CTE query, classified each order from net receipts minus its item value, and delivered two real CSV files. You read correctly quoted labels and distinguished balanced stall totals from individually settled orders.