# PostgreSQL Orders: Reporting & Query Plans

TechBuilt Open School — Free, self-guided technical project kit

- **Track:** Databases & SQL
- **Level:** Intermediate
- **Access:** No account, signup, payment or enrollment required
- **Format:** Guided plan and runnable starter example, not a complete solution or a live class
- **Online guide:** https://techbuiltos.online/projects/postgresql-order-analytics

## Project goal

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

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

## Before you begin

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

## Local setup

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 example — orders-postgresql.sql

The sample below is a starting point. It may require the prerequisites above. Complete the subsequent milestones to finish your own project.

```sql
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;
```

## 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

- [ ] 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 extensions

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

## Related learning

- Individual course roadmap: https://techbuiltos.online/courses/postgresql
- Individual course roadmap: https://techbuiltos.online/courses/sql

- Specialization: https://techbuiltos.online/specializations/database-developer
- Specialization: https://techbuiltos.online/specializations/backend-developer

---

This original self-study project is independent of paid cohorts. Review any external library documentation before use. Do not commit real credentials, private client files or personal data to public repositories.
