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
- Create a disposable local development database.
- Run the starter SQL in psql or a PostgreSQL query tool.
- Inspect each step before testing with larger synthetic datasets.
Starter code
orders-postgresql.sqlThis 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
- Model customers and orders using primary and foreign keys.
- Write grouped revenue totals with LEFT JOIN and COALESCE for customers without orders.
- Explain why COUNT(*) and COUNT(o.id) differ for outer joins.
- Use EXPLAIN (ANALYZE, BUFFERS) on a SELECT query and record the plan.
- 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.