Phase Project: Sales Reporting Database — SQL Course

What you'll learn

Model customers, products, orders, and payments, then deliver a tested reporting pack for business stakeholders. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Normalized sales model to a realistic data question
Apply Complex joins to a realistic data question
Apply KPIs to a realistic data question
Apply Subquery use to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Metric definition	A precise statement of numerator, denominator, grain, time, and exclusions	Agree on meaning before optimizing SQL
Reconciliation	Independent totals proving report completeness	Tie detailed outputs back to authoritative control totals
Report contract	Stable columns and semantics consumed by people or tools	Version breaking changes and document caveats

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the sales reporting pack question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Reconcile order and payment totals

Full outer comparison reveals missing orders, missing payments, and mismatched amounts.

reconcile_sales.sql

SELECT
  COALESCE(o.order_id, p.order_id) AS order_id,
  o.ordered,
  p.paid,
  COALESCE(p.paid, 0) - COALESCE(o.ordered, 0) AS difference
FROM report.order_totals AS o
FULL JOIN report.payment_totals AS p USING (order_id)
WHERE COALESCE(o.ordered, 0) <> COALESCE(p.paid, 0);

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A polished report without reconciliation can present incorrect numbers with high confidence.

Independent workshop

Deliver a stakeholder-ready sales reporting database with six KPIs, detail drill-downs, and reconciliation evidence.

Your finished workshop must include:

Normalized sales model
Complex joins
KPIs
Subquery use
Set comparisons
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Metric definition: Agree on meaning before optimizing SQL
Reconciliation: Tie detailed outputs back to authoritative control totals
Report contract: Version breaking changes and document caveats

Quick check

1. Which rule best applies to Metric definition?

2. Which rule best applies to Reconciliation?

3. Which rule best applies to Report contract?

Next: Common Table Expressions & Query Decomposition