Skip to main content
Databases & SQLIntermediateFree to access

PostgreSQL Orders: Reporting & Query Plans

Explore order revenue by customer, then compare simple query plans to understand when an index helps.

Download free Markdown guideNo email, account or payment required · .md text format

What you will build

Practice PostgreSQL schema design, aggregates and evidence-based optimization rather than guessing performance.

Before you begin

  • PostgreSQL 14+ and psql or pgAdmin
  • SELECT, JOIN, GROUP BY and CREATE TABLE basics

Local setup instructions

  1. Create a disposable local development database.
  2. Run the starter SQL in psql or a PostgreSQL query tool.
  3. Inspect each step before testing with larger synthetic datasets.

Starter code

orders-postgresql.sql

This is a working starting example, not the finished project. Follow the setup above and complete each milestone to build your own solution.

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total NUMERIC(12, 2) NOT NULL CHECK (total >= 0)
);
INSERT INTO customers (name) VALUES ('Aisha'), ('Hamza');
INSERT INTO orders (customer_id, total) VALUES (1, 250.00), (1, 450.00);

SELECT c.name, COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY revenue DESC;

Project build milestones

  1. Model customers and orders using primary and foreign keys.
  2. Write grouped revenue totals with LEFT JOIN and COALESCE for customers without orders.
  3. Explain why COUNT(*) and COUNT(o.id) differ for outer joins.
  4. Use EXPLAIN (ANALYZE, BUFFERS) on a SELECT query and record the plan.
  5. Add a relevant index, repeat the plan, and state whether evidence shows benefit.

Completion checklist

Use these checks to self-review the finished project.

  • Revenue totals are correct for multiple orders per customer.
  • Customers without orders remain in the report.
  • All performance claims refer to observed execution plans and row counts.
  • The SQL can be recreated without production credentials.

Optional challenges

  • Compare plans after generating a larger synthetic dataset.
  • Document data migration, rollback and backup procedures.