E-Commerce Orders and Inventory Schema

Project roadmap

1. 01

   Design the full schema

   ~2h

   Create customers, products, categories, orders, order_items, and inventory tables with foreign keys.

2. 02

   Insert realistic sample data

   ~1.5h

   Populate all tables with a coherent set of customers, products, and multi-item orders.

3. 03

   Report revenue by category

   ~2h

   Write a multi-table join query computing total revenue grouped by product category.

4. 04

   Find inactive customers

   ~1.5h

   Use a subquery to find customers with no orders in the last 90 days.

5. 05

   Write a stock-decrement transaction

   ~2h

   Write a transaction that inserts an order and decrements inventory atomically, rolling back on insufficient stock.

6. 06

   Add indexes and analyze plans

   ~1.5h

   Add appropriate indexes and use EXPLAIN to verify the reporting queries use them.

Resources

• DocsPostgreSQL Documentation