Skip to content
SQLProjects and Interview Preparationadvanced35 min read

Building an E-commerce Database

Build an end-to-end PostgreSQL e-commerce database with customers, products, inventory, orders, order items, payments, and analytical queries for revenue and product performance.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Design a normalized e-commerce schema for customers, products, orders, order items, inventory, and payments
  • ✓Enforce primary keys, foreign keys, unique constraints, check constraints, and appropriate data types
  • ✓Preserve historical product prices in order items and distinguish order status from payment status
  • ✓Use transactions and conditional updates to prevent inventory from becoming negative
  • ✓Write analytical queries for monthly revenue, top-selling products, average order value, and inactive customers
  • ✓Choose indexes based on query patterns and inspect query execution plans

Introduction to the E-commerce Database

An e-commerce application allows customers to browse products, place orders, pay for purchases, and receive their items. The database must preserve accurate records even when product prices change, payments fail, or inventory becomes limited.

In this project, we will build a simplified PostgreSQL database that supports these operations.

## Business requirements

Our database must store:
- Customer details.
- Products and their current prices.
- Available inventory.
- Customer orders.
- The products, quantities, and prices included in each order.
- Payment attempts and their statuses.

It should also answer questions such as:
- What is the monthly revenue from paid orders?
- Which products sell the most?
- Which customers have not placed a recent order?
- What is the average order value?
- How much inventory remains?
- Which orders have not been paid?

## Main tables

1. customers — one row per customer.
2. products — one row per product.
3. inventory — current stock for each product.
4. orders — one row per order.
5. order_items — the products and quantities in each order.
6. payments — payment attempts associated with orders.

The relationship between orders and products is many-to-many, represented by order_items.

One order can contain several products, and one product can appear in many orders.

## Important design decision: historical prices

Suppose a product costs ₹1,000 today, but a customer bought it last month for ₹800. If the order item refers only to the current product price, historical order totals could change when the product price is updated.

Therefore, order_items stores unit_price, which is the price charged for that particular order item.

## Important design decision: payment status

An order can be pending while a payment is being processed. A payment can also fail even though the order record remains in the database.

For this project, order status and payment status are separate. We will use successful payment records to calculate a clearly defined paid-order revenue metric rather than assuming every placed order generates revenue.

Designing the Database Schema

We will create tables in dependency order so that referenced tables exist before their foreign keys are defined.

The schema uses:
- BIGINT identity columns for generated IDs.
- NUMERIC for prices and payment amounts.
- CHECK constraints to reject negative prices, quantities, and stock.
- Foreign keys to enforce valid relationships.
- Unique constraints to prevent duplicate customer emails and product SKUs.
- A composite primary key on order_items to allow one row per product in each order.

For simplicity, each order can contain a given product only once. If the customer adds the same product again, the application should update that item's quantity.

The inventory table has one row per product. A larger system might instead track inventory by warehouse, store, or stock location.

Create the E-commerce Schema

Create the E-commerce Schema
sql
CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(200) NOT NULL,
    current_price NUMERIC(12, 2) NOT NULL
        CHECK (current_price >= 0),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY
        REFERENCES products(product_id),
    stock_quantity INTEGER NOT NULL
        CHECK (stock_quantity >= 0),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL
        REFERENCES customers(customer_id),
    order_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN (
            'pending',
            'confirmed',
            'shipped',
            'delivered',
            'cancelled'
        ))
);
 
CREATE TABLE order_items (
    order_id BIGINT NOT NULL
        REFERENCES orders(order_id),
    product_id BIGINT NOT NULL
        REFERENCES products(product_id),
    quantity INTEGER NOT NULL
        CHECK (quantity > 0),
    unit_price NUMERIC(12, 2) NOT NULL
        CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);
 
CREATE TABLE payments (
    payment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL
        REFERENCES orders(order_id),
    amount NUMERIC(12, 2) NOT NULL
        CHECK (amount > 0),
    status VARCHAR(20) NOT NULL
        CHECK (status IN (
            'pending',
            'succeeded',
            'failed',
            'refunded'
        )),
    payment_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    transaction_reference VARCHAR(100) UNIQUE
);
Output
CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE

The six tables enforce important structural rules. The order_items composite primary key prevents duplicate product rows within one order. The payment transaction reference is unique when supplied; PostgreSQL permits multiple NULL values in a standard unique constraint. These constraints do not by themselves implement the complete order-placement or payment workflow.

Understanding the Relationships and Constraints

## Customer and order relationship

One customer can place many orders. Each order references exactly one customer.

## Order and order-item relationship

An order can contain many order items. Every order item references one order and one product.

The composite primary key (order_id, product_id) prevents a product from appearing twice as separate rows in the same order.

## Product and inventory relationship

Each product has one inventory row in this simplified model. The inventory primary key is also a foreign key to products.

The CHECK constraint on stock_quantity prevents negative stock from being stored.

## Order and payment relationship

One order can have multiple payment records, such as an initial failed attempt followed by a successful retry. The application must define which payment records count toward paid revenue and how refunds are represented.

This schema records payment attempts, but it does not fully model partial captures, split payments, chargebacks, or multiple refund events. Those features would need additional business rules and tables.

## Why use NUMERIC for money?

NUMERIC(12, 2) stores decimal values with fixed precision and scale, which is suitable for many currency calculations. Floating-point types such as REAL and DOUBLE PRECISION can introduce rounding differences in monetary calculations.

For multi-currency systems, also store the currency code and define how conversions and rounding work.

Inspect Foreign-Key Relationships

Inspect Foreign-Key Relationships
sql
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
   AND tc.constraint_schema = kcu.constraint_schema
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
   AND ccu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.constraint_schema = 'public'
  AND tc.table_name IN (
      'inventory',
      'orders',
      'order_items',
      'payments'
  )
ORDER BY tc.table_name, kcu.column_name;
Output
The result lists the foreign keys from: inventory.product_id -> products.product_id orders.customer_id -> customers.customer_id order_items.order_id -> orders.order_id order_items.product_id -> products.product_id payments.order_id -> orders.order_id

The query inspects PostgreSQL metadata. The listed relationships assume the tables were created in the public schema as shown. The actual result includes one row per foreign-key column.

Inserting Sample Customers, Products, and Inventory

Now add sample data in dependency order.

We insert customers and products first, followed by inventory rows. The sample products have different prices and stock quantities so the analytical queries produce useful results.

The SQL uses explicit columns. The displayed IDs are expected for newly created, empty tables whose identity sequences have not been used before. In a real application, never assume that a newly inserted record always receives a particular ID.

Insert Customers, Products, and Stock

Insert Customers, Products, and Stock
sql
INSERT INTO customers (full_name, email) VALUES
    ('Aarav Sharma', 'aarav@example.com'),
    ('Diya Verma', 'diya@example.com'),
    ('Kabir Singh', 'kabir@example.com'),
    ('Meera Gupta', 'meera@example.com');
 
INSERT INTO products (sku, product_name, current_price) VALUES
    ('KB101', 'Mechanical Keyboard', 2500.00),
    ('MS201', 'Wireless Mouse', 1200.00),
    ('HD301', 'USB-C Hub', 1800.00),
    ('MN401', 'Monitor Stand', 1500.00),
    ('CB501', 'USB-C Cable', 500.00);
 
INSERT INTO inventory (product_id, stock_quantity) VALUES
    (1, 25),
    (2, 40),
    (3, 15),
    (4, 10),
    (5, 100);
Output
INSERT 0 4 INSERT 0 5 INSERT 0 5

The sample data assumes fresh identity sequences, producing customer IDs 1–4 and product IDs 1–5. If the database already contains records, use RETURNING or query by stable unique values such as email and SKU to obtain the actual IDs.

Creating Orders and Order Items

An order header stores information shared by the whole order, such as its customer, date, and status.

The order_items table stores each product, quantity, and unit price. The line total is quantity multiplied by unit_price.

We deliberately do not store a separate line_total column in this introductory schema because it can be calculated from the other values. This avoids having to keep redundant values synchronized.

The order's total is also derived from its order items. If a production system stores cached totals for performance, it must ensure those totals remain consistent with item changes, discounts, taxes, and refunds.

Insert Orders and Their Items

Insert Orders and Their Items
sql
INSERT INTO orders (customer_id, order_date, status) VALUES
    (1, '2026-09-05 10:00:00+00', 'delivered'),
    (2, '2026-09-12 12:30:00+00', 'delivered'),
    (1, '2026-10-02 09:15:00+00', 'confirmed'),
    (3, '2026-10-10 16:45:00+00', 'pending'),
    (2, '2026-10-15 14:00:00+00', 'cancelled');
 
INSERT INTO order_items
    (order_id, product_id, quantity, unit_price)
VALUES
    (1, 1, 1, 2500.00),
    (1, 2, 2, 1200.00),
    (2, 3, 1, 1800.00),
    (2, 5, 2, 500.00),
    (3, 1, 1, 2400.00),
    (3, 3, 1, 1800.00),
    (4, 2, 1, 1200.00),
    (5, 4, 1, 1500.00);
Output
INSERT 0 5 INSERT 0 8

The unit price for the keyboard in order 3 is ₹2,400, even though the product's current catalog price is ₹2,500. This demonstrates why order_items stores the historical price charged. The sample stock values are illustrative; inserting order items alone does not reserve or deduct inventory.

Recording Payment Attempts

Payments are separate records because an order may have more than one payment attempt.

Our sample contains successful payments for delivered and confirmed orders, a pending payment, and a failed payment. The cancelled order has no successful payment.

For this lesson, a successful payment means the payment provider reported a successful payment record. A real payment integration must also verify the provider's response, handle duplicate notifications idempotently, and reconcile refunds and chargebacks.

Insert Sample Payments

Insert Sample Payments
sql
INSERT INTO payments
    (order_id, amount, status, payment_date, transaction_reference)
VALUES
    (1, 4900.00, 'succeeded', '2026-09-05 10:05:00+00', 'TXN-1001'),
    (2, 2800.00, 'succeeded', '2026-09-12 12:35:00+00', 'TXN-1002'),
    (3, 4200.00, 'succeeded', '2026-10-02 09:20:00+00', 'TXN-1003'),
    (4, 1200.00, 'pending',   '2026-10-10 16:50:00+00', 'TXN-1004'),
    (5, 1500.00, 'failed',    '2026-10-15 14:05:00+00', 'TXN-1005');
Output
INSERT 0 5

The sample payments match the corresponding order totals. For a production system, the database should have explicit policies for partial payments, multiple successful payment captures, refunds, and reconciliation. The simple revenue query later in this lesson uses one successful payment per paid order.

Viewing Orders with Customer and Product Details

Joins combine related information stored in separate tables.

To show every item in every order, join:
- orders to customers,
- orders to order_items,
- order_items to products.

The line total is calculated from quantity and the stored unit price, not from the product's current catalog price.

This distinction protects the historical order amount when a product's current price changes.

Order Detail Report

Order Detail Report
sql
SELECT
    o.order_id,
    c.full_name,
    o.status AS order_status,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders AS o
JOIN customers AS c
    ON c.customer_id = o.customer_id
JOIN order_items AS oi
    ON oi.order_id = o.order_id
JOIN products AS p
    ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_name;
Output
order_id | full_name | order_status | product_name | quantity | unit_price | line_total ---------+--------------+--------------+----------------------+----------+------------+----------- 1 | Aarav Sharma | delivered | Mechanical Keyboard | 1 | 2500.00 | 2500.00 1 | Aarav Sharma | delivered | Wireless Mouse | 2 | 1200.00 | 2400.00 2 | Diya Verma | delivered | USB-C Cable | 2 | 500.00 | 1000.00 2 | Diya Verma | delivered | USB-C Hub | 1 | 1800.00 | 1800.00 3 | Aarav Sharma | confirmed | Mechanical Keyboard | 1 | 2400.00 | 2400.00 3 | Aarav Sharma | confirmed | USB-C Hub | 1 | 1800.00 | 1800.00 4 | Kabir Singh | pending | Wireless Mouse | 1 | 1200.00 | 1200.00 5 | Diya Verma | cancelled | Monitor Stand | 1 | 1500.00 | 1500.00

Each row represents one order item. An order with several products appears on multiple rows. The cancelled order still appears because this is an order-detail report, not a revenue report.

Calculating Order Totals

To calculate each order's merchandise total, sum its line totals.

GROUP BY groups rows belonging to the same order. SUM then adds the line totals.

This example includes orders without items by using LEFT JOIN. COALESCE changes the NULL sum for an empty order to zero.

This total represents merchandise only. Shipping fees, discounts, taxes, and refunds are not included in this simplified project.

Calculate the Total for Every Order

Calculate the Total for Every Order
sql
SELECT
    o.order_id,
    c.full_name,
    o.status,
    COALESCE(
        SUM(oi.quantity * oi.unit_price),
        0
    ) AS order_total
FROM orders AS o
JOIN customers AS c
    ON c.customer_id = o.customer_id
LEFT JOIN order_items AS oi
    ON oi.order_id = o.order_id
GROUP BY o.order_id, c.full_name, o.status
ORDER BY o.order_id;
Output
order_id | full_name | status | order_total ---------+--------------+-----------+------------ 1 | Aarav Sharma | delivered | 4900.00 2 | Diya Verma | delivered | 2800.00 3 | Aarav Sharma | confirmed | 4200.00 4 | Kabir Singh | pending | 1200.00 5 | Diya Verma | cancelled | 1500.00

The order total is derived from quantity multiplied by unit_price. Cancelled orders remain visible in this operational report; filter them out only when the reporting definition requires it.

Monthly Revenue Analytics

Revenue reports must define what they measure.

For this project, monthly paid-order revenue means the sum of successful payment amounts, grouped by the payment date. We count a payment as successful when payments.status = 'succeeded'.

This is a deliberately simplified cash-receipt metric. It is not a complete accounting definition of recognized revenue. Refunds, chargebacks, tax, shipping, partial captures, and payment reconciliation need explicit policies in a real business.

We use the payment date rather than the order date because this report analyzes successful payment activity by month.

Monthly Successful Payment Totals

Monthly Successful Payment Totals
sql
SELECT
    DATE_TRUNC('month', payment_date)::date AS revenue_month,
    SUM(amount) AS successful_payment_total
FROM payments
WHERE status = 'succeeded'
GROUP BY DATE_TRUNC('month', payment_date)::date
ORDER BY revenue_month;
Output
revenue_month | successful_payment_total --------------+------------------------ 2026-09-01 | 7700.00 2026-10-01 | 4200.00

September has successful payments of ₹4,900 and ₹2,800, totaling ₹7,700. October has one successful payment of ₹4,200. The pending and failed payments are excluded. DATE_TRUNC groups timestamps by calendar month using the session's time-zone behavior; define a reporting time zone explicitly for a multi-region business.

Finding Top-Selling Products

A product performance report can rank products by units sold.

The query below counts quantities from orders that are not cancelled. This is a simple operational definition of units sold: it excludes cancelled orders but does not wait for payment success or delivery.

That definition is useful for a basic exercise, but a real business must decide whether to count only paid, shipped, or delivered orders. Refunds and returns may also require subtracting returned quantities.

Rank Products by Units Ordered

Rank Products by Units Ordered
sql
SELECT
    p.product_id,
    p.product_name,
    COALESCE(SUM(oi.quantity), 0) AS units_ordered,
    COALESCE(
        SUM(oi.quantity * oi.unit_price),
        0
    ) AS merchandise_value
FROM products AS p
LEFT JOIN order_items AS oi
    ON oi.product_id = p.product_id
LEFT JOIN orders AS o
    ON o.order_id = oi.order_id
   AND o.status <> 'cancelled'
WHERE oi.order_id IS NULL
   OR o.order_id IS NOT NULL
GROUP BY p.product_id, p.product_name
ORDER BY units_ordered DESC, p.product_name;
Output
product_id | product_name | units_ordered | merchandise_value -----------+----------------------+---------------+------------------ 2 | Wireless Mouse | 3 | 3600.00 1 | Mechanical Keyboard | 2 | 4900.00 3 | USB-C Hub | 2 | 3600.00 5 | USB-C Cable | 2 | 1000.00 4 | Monitor Stand | 0 | 0.00

The query includes products with no qualifying order items. The condition retains an item when its order is not cancelled and also retains products with no items. This report counts non-cancelled ordered quantities, not necessarily paid or delivered units. A simpler report can use INNER JOIN and a WHERE filter when products with zero sales do not need to appear.

Identifying Inactive Customers

A customer inactivity report identifies customers who have not placed a recent order.

There are several possible definitions:
- Customers who have never ordered.
- Customers whose last order is older than a specified period.
- Customers who have not made a paid purchase recently.

This example uses the second definition: customers whose most recent order date is before a supplied cutoff date, including customers who have never ordered.

The cutoff is explicit rather than using an arbitrary current date, so the report can be reproduced and tested.

Find Customers with No Recent Orders

Find Customers with No Recent Orders
sql
WITH customer_activity AS (
    SELECT
        c.customer_id,
        c.full_name,
        c.email,
        MAX(o.order_date) AS last_order_date
    FROM customers AS c
    LEFT JOIN orders AS o
        ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.full_name, c.email
)
SELECT
    customer_id,
    full_name,
    email,
    last_order_date
FROM customer_activity
WHERE last_order_date IS NULL
   OR last_order_date < TIMESTAMPTZ '2026-10-01 00:00:00+00'
ORDER BY customer_id;
Output
customer_id | full_name | email | last_order_date ------------+------------+--------------------+---------------- 4 | Meera Gupta | meera@example.com | NULL

Meera has never placed an order, so MAX(order_date) is NULL. The other customers have orders dated on or after the cutoff. If the intended definition is based on paid purchases rather than orders, join or filter payment records according to the required payment policy.

Calculating Average Order Value

Average order value (AOV) is often calculated as eligible order value divided by the number of eligible orders.

The exact definition varies. For this exercise, we use the total merchandise value of orders whose status is delivered, divided by the number of delivered orders.

This is not the same as average successful payment amount. If an order has multiple payments, calculating the average directly after joining payments to order items can duplicate item totals. Aggregate at the correct level before joining different one-to-many relationships.

Calculate Delivered-Order Average Value

Calculate Delivered-Order Average Value
sql
WITH order_totals AS (
    SELECT
        o.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders AS o
    JOIN order_items AS oi
        ON oi.order_id = o.order_id
    WHERE o.status = 'delivered'
    GROUP BY o.order_id
)
SELECT
    COUNT(*) AS delivered_orders,
    SUM(order_total) AS delivered_merchandise_total,
    ROUND(AVG(order_total), 2) AS average_order_value
FROM order_totals;
Output
delivered_orders | delivered_merchandise_total | average_order_value -----------------+-----------------------------+-------------------- 2 | 7700.00 | 3850.00

The two delivered orders total ₹4,900 and ₹2,800. Their average is (₹4,900 + ₹2,800) / 2 = ₹3,850. The CTE computes one total per order before calculating the average.

Safely Updating Inventory

Inventory is a critical consistency problem.

A naive workflow might:
1. Read stock quantity.
2. Check whether enough stock exists.
3. Update the stock quantity.

Two requests could read the same stock quantity at nearly the same time. Both might believe there is enough inventory and oversell the product.

PostgreSQL supports conditional updates that perform the stock check and decrement in one statement. The update succeeds only when sufficient stock exists.

The application must check whether a row was returned. No returned row means the product did not exist in inventory or did not have sufficient stock.

In a complete order-placement transaction, stock reservation, order creation, and order-item creation must be coordinated. A payment provider call should not be treated as an ordinary SQL statement; payment workflows usually need idempotency, reconciliation, and explicit handling of failures.

Atomically Decrement Available Stock

Atomically Decrement Available Stock
sql
UPDATE inventory
SET
    stock_quantity = stock_quantity - 2,
    updated_at = CURRENT_TIMESTAMP
WHERE product_id = 1
  AND stock_quantity >= 2
RETURNING product_id, stock_quantity;
Output
product_id | stock_quantity -----------+--------------- 1 | 23

The output assumes product 1 initially has 25 units. PostgreSQL performs the conditional update atomically. If stock is below 2, the statement updates no rows and returns no result. In application code, treat that as insufficient stock or a missing inventory record.

Using Transactions for Order Placement

A simplified order-placement operation can use a transaction to group the stock decrement, order insertion, and order-item insertion.

If a required step fails, the transaction can be rolled back so the database does not retain a partially created order.

The example below demonstrates the pattern for one product and assumes:
- Customer ID 4 exists.
- Product ID 5 exists.
- Inventory has enough stock.
- The SQL statements are run by an application that checks every returned row.

The application should generate IDs using RETURNING and pass those IDs into later statements. The SQL here illustrates the transaction flow; a production order workflow must also handle multiple items, price validation, cancellation, reservation expiry, and payment integration.

Transactional Single-Item Order Pattern

Transactional Single-Item Order Pattern
sql
BEGIN;
 
-- Reserve one unit only if stock is available.
UPDATE inventory
SET
    stock_quantity = stock_quantity - 1,
    updated_at = CURRENT_TIMESTAMP
WHERE product_id = 5
  AND stock_quantity >= 1
RETURNING product_id, stock_quantity;
 
-- Continue only if the inventory update returned a row.
-- In application code, capture the generated order ID.
INSERT INTO orders (customer_id, status)
VALUES (4, 'pending')
RETURNING order_id;
 
-- Use the returned order_id in the following statement.
-- The example assumes the generated order ID is 6.
INSERT INTO order_items
    (order_id, product_id, quantity, unit_price)
VALUES
    (6, 5, 1, 500.00);
 
COMMIT;
Output
BEGIN UPDATE 1 INSERT 0 1 INSERT 0 1 COMMIT

The displayed order ID is illustrative and must not be hard-coded in production. The application must roll back if the inventory update returns no row or if any required insert fails. A transaction alone does not guarantee that all application-level business rules have been implemented.

Indexes and Query Performance

Indexes can speed up common lookup and reporting queries, but they add storage and write overhead.

PostgreSQL automatically creates indexes for primary keys and unique constraints. It does not automatically create an index on every foreign-key column.

Useful candidates for this schema include:
- orders(customer_id, order_date) for a customer's order history.
- orders(order_date) for date-range reports.
- order_items(product_id) for product-level sales analysis.
- payments(payment_date) for payment-date reports.

The correct indexes depend on actual filters, joins, ordering, and data distribution. For large tables, a partial index can be useful when reports repeatedly filter to a particular status, but the benefit should be measured.

Create Indexes for Common Queries

Create Indexes for Common Queries
sql
CREATE INDEX idx_orders_customer_date
    ON orders(customer_id, order_date DESC);
 
CREATE INDEX idx_orders_order_date
    ON orders(order_date);
 
CREATE INDEX idx_order_items_product
    ON order_items(product_id);
 
CREATE INDEX idx_payments_date
    ON payments(payment_date);
 
EXPLAIN
SELECT
    order_id,
    order_date,
    status
FROM orders
WHERE customer_id = 1
ORDER BY order_date DESC;
Output
CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX QUERY PLAN ------------------------------------------------ [PostgreSQL displays the selected execution plan]

The composite index can support filtering by customer_id and ordering by order_date. EXPLAIN shows the chosen plan, which may use an index scan or a sequential scan depending on table size and statistics. EXPLAIN ANALYZE executes the query and provides measured runtime information.

Exercises: Extend the Project

Complete the following exercises using the schema and sample data.

## Exercise 1: Customer order history
Display each customer's name, order ID, order date, and order status. Include customers who have never placed an order.

Hint: Start from customers and use LEFT JOIN.

## Exercise 2: Products with low stock
Find active products whose stock quantity is less than 10.

Hint: Join products to inventory.

## Exercise 3: Revenue by month
Calculate successful payment totals by month and sort the months chronologically.

Hint: Group payments by DATE_TRUNC('month', payment_date) and filter to succeeded.

## Exercise 4: Top three products by units ordered
Rank products by quantity ordered, excluding cancelled orders.

Hint: Aggregate quantities before applying ORDER BY and LIMIT.

## Exercise 5: Customers who have never purchased
Find customers who have no successful payment associated with any of their orders.

Hint: Use NOT EXISTS. Decide whether a successful payment of any amount is sufficient for this simplified exercise.

## Exercise 6: Inventory safety
Attempt to decrement inventory by more units than are available. Verify that the conditional UPDATE returns no row and leaves stock unchanged.

## Exercise 7: Course correction for a production design
Extend the schema to handle multiple warehouses and inventory reservations.

Hint: Consider warehouses, inventory by (warehouse_id, product_id), and a reservation table with a lifecycle.

## Exercise 8: Payment integrity
Explain why an order can have several payment attempts and why adding all successful payments together can overstate revenue if a provider retries or duplicates events.

Hint: Consider idempotency keys and unique provider transaction references.

Exercise Solutions

Use these reference queries to compare with your answers.

## Solution 1: Customer order history

LEFT JOIN retains customers without orders.

## Solution 2: Products with low stock

Join inventory to products and filter active products with stock below 10.

## Solution 4: Top products

This solution counts quantities from non-cancelled orders. It includes products with no qualifying sales and returns the top three by units ordered.

## Solution 5: Customers without successful payments

NOT EXISTS checks for the absence of any successful payment associated with the customer's orders. It does not claim that the customer has never browsed or attempted checkout.

Reference Queries

Reference Queries
sql
-- Solution 1: All customers and their orders
SELECT
    c.customer_id,
    c.full_name,
    o.order_id,
    o.order_date,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_date;
 
-- Solution 2: Active products with fewer than 10 units
SELECT
    p.sku,
    p.product_name,
    i.stock_quantity
FROM products AS p
JOIN inventory AS i
    ON i.product_id = p.product_id
WHERE p.is_active = TRUE
  AND i.stock_quantity < 10
ORDER BY i.stock_quantity, p.product_name;
 
-- Solution 3: Successful payment totals by month
SELECT
    DATE_TRUNC('month', payment_date)::date AS revenue_month,
    SUM(amount) AS successful_payment_total
FROM payments
WHERE status = 'succeeded'
GROUP BY DATE_TRUNC('month', payment_date)::date
ORDER BY revenue_month;
 
-- Solution 4: Top three products by non-cancelled units ordered
SELECT
    p.product_id,
    p.product_name,
    COALESCE(SUM(oi.quantity), 0) AS units_ordered
FROM products AS p
LEFT JOIN order_items AS oi
    ON oi.product_id = p.product_id
LEFT JOIN orders AS o
    ON o.order_id = oi.order_id
   AND o.status <> 'cancelled'
WHERE oi.order_id IS NULL
   OR o.order_id IS NOT NULL
GROUP BY p.product_id, p.product_name
ORDER BY units_ordered DESC, p.product_name
LIMIT 3;
 
-- Solution 5: Customers without successful payments
SELECT
    c.customer_id,
    c.full_name,
    c.email
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    JOIN payments AS p
        ON p.order_id = o.order_id
    WHERE o.customer_id = c.customer_id
      AND p.status = 'succeeded'
)
ORDER BY c.customer_id;
Output
Exercise 2: sku | product_name | stock_quantity ------+---------------------+--------------- MN401 | Monitor Stand | 10 No rows are returned for Exercise 2 with the original sample stock values, because the filter is strictly less than 10. Exercise 3: 2026-09-01 | 7700.00 2026-10-01 | 4200.00 Exercise 5: customer_id | full_name ------------+------------ 3 | Kabir Singh 4 | Meera Gupta

The Exercise 2 heading is shown to illustrate the threshold; with the sample stock of 10 for Monitor Stand, the strict < 10 filter correctly returns no rows. For Exercise 5, Kabir and Meera have no successful payment records. The exercise 4 query returns the top three products by non-cancelled units ordered. These outputs assume the original sample data before any optional stock update or additional order transaction.

Knowledge Check

1. Why does order_items store unit_price?
A. To replace products
B. To preserve the price charged at the time of purchase
C. To calculate inventory automatically
D. To identify the customer

2. Which type is appropriate for many fixed-precision monetary values?
A. BOOLEAN
B. NUMERIC(12, 2)
C. DATE
D. REAL in every case

3. What does a foreign key enforce?
A. Referential integrity
B. Monthly revenue
C. Product ranking
D. Automatic payment processing

4. Why should inventory decrement use a condition such as stock_quantity >= requested_quantity?
A. To prevent the update from reducing stock below the available amount
B. To increase every product's price
C. To remove foreign keys
D. To calculate tax

5. Why are payment status and order status separate?
A. They always change together
B. Payment processing and fulfillment are different business processes
C. Payment status identifies a product
D. Order status replaces the customer table

6. What does a LEFT JOIN preserve?
A. Only rows with matching records
B. All rows from the left table, even when no right-side row matches
C. Only rows containing NULL
D. Only the newest record

7. What is the purpose of an index?
A. To guarantee that every query is faster
B. To potentially speed up qualifying lookups, joins, and ordering
C. To replace transactions
D. To eliminate constraints

8. Why should a revenue metric have an explicit definition?
A. All revenue metrics mean the same thing
B. Order status, payment success, refunds, and accounting policies affect what should be counted
C. SQL cannot sum payments
D. Product prices never change

Answer Key

Answer Key
text
1. B — Preserves the historical price charged.
2. B — NUMERIC supports fixed precision and scale.
3. A — Enforces referential integrity.
4. A — Makes the stock check part of the update condition.
5. B — Payment and fulfillment have separate lifecycles.
6. B — Preserves unmatched left-side rows.
7. B — Can improve queries when appropriate.
8. B — A metric must define its business meaning.

Review the schema, transaction, and analytics sections for the reasoning behind each answer. Pay particular attention to the difference between operational order totals and payment-based revenue.

SQL Interview Questions and Answers

## 1. Why do we need an order_items table?

An order can contain multiple products, and a product can appear in multiple orders. The junction table implements this many-to-many relationship and stores relationship-specific details such as quantity and historical unit price.

## 2. Why store the unit price in order_items?

The product's current price may change after a purchase. Storing the price charged preserves the historical value used to calculate the original order total.

## 3. How would you calculate monthly revenue?

First define the metric. For this project, sum successful payment amounts grouped by payment month. A real accounting report may need refund handling, taxes, currency conversion, and recognized-revenue rules.

## 4. How can you avoid overselling inventory?

Use a conditional UPDATE that decrements stock only when enough units remain. Check whether the update affected a row, and coordinate inventory reservation with order creation in a transaction.

## 5. Why use a transaction for order placement?

It allows related database changes to commit together or roll back together. If inventory reservation or a required insert fails, the application should not commit a partial order.

## 6. What is the difference between order status and payment status?

Order status describes the fulfillment lifecycle, such as pending, shipped, or delivered. Payment status describes the payment attempt, such as pending, succeeded, or failed.

## 7. How would you find customers who never purchased?

Use NOT EXISTS to check for the absence of a qualifying purchase or successful payment. Define carefully whether a purchase means an order, a successful payment, or a delivered order.

## 8. Why can joining payments directly to order_items produce incorrect totals?

An order may have multiple items and multiple payment records. Joining both one-to-many relationships can multiply rows and duplicate amounts. Aggregate each relationship at the correct level before combining the results.

## 9. Why use NUMERIC instead of floating-point types for money?

NUMERIC provides exact decimal arithmetic within its declared precision and scale. Floating-point values can introduce representation and rounding errors that are undesirable for financial calculations.

## 10. What is a composite primary key?

It is a primary key consisting of multiple columns. Here, (order_id, product_id) uniquely identifies one product line per order.

## 11. Does a foreign key automatically create an index on the referencing column in PostgreSQL?

No. PostgreSQL creates indexes for primary keys and unique constraints, but does not automatically index every referencing foreign-key column. Add indexes when workload and performance measurements justify them.

## 12. How should a production payment system handle duplicate notifications?

Use idempotent processing, unique provider transaction or event identifiers, transactional state changes, and reconciliation. Do not blindly insert or sum duplicate payment notifications.

## 13. How would you model multiple warehouses?

Create a warehouses table and make inventory unique by (warehouse_id, product_id). Add reservations or stock movements if the application needs to track stock allocation, transfers, and audit history.

## 14. How do you know whether an index helps?

Inspect EXPLAIN plans and use EXPLAIN ANALYZE with representative data and query patterns. Compare execution behavior rather than assuming an index will always be used.

Common Mistakes and How to Avoid Them

## Mistake 1: Calculating historical orders from the current product price

Current catalog prices change. Use order_items.unit_price for historical order calculations.

## Mistake 2: Storing money in floating-point columns

Floating-point arithmetic can introduce precision differences. Use an appropriate fixed-precision NUMERIC type or a carefully designed integer-minor-unit model.

## Mistake 3: Updating inventory without an atomic stock condition

A separate read followed by an unconditional decrement can lead to overselling under concurrency. Perform the stock check and decrement together.

## Mistake 4: Assuming every order generates revenue

Pending, failed, and cancelled orders should not automatically count as successful payment revenue. Define the reporting metric first.

## Mistake 5: Joining several one-to-many relationships before aggregating

Joining order items and payments can multiply rows. Aggregate each relationship at the appropriate level before combining totals.

## Mistake 6: Confusing payment status with order status

A payment can succeed while an order is not yet delivered. Keep their states separate and define valid business transitions.

## Mistake 7: Treating every successful payment as a distinct purchase

Retries, duplicated provider notifications, partial captures, and refunds require explicit rules. Use idempotency and reconciliation rather than blindly summing every event.

## Mistake 8: Assuming foreign keys automatically create all useful indexes

PostgreSQL does not automatically index every referencing foreign-key column. Choose indexes based on joins, filters, and ordering patterns.

## Mistake 9: Hard-coding generated IDs

Identity values vary by database state. Capture generated identifiers with RETURNING or resolve records through stable unique values.

## Mistake 10: Calling a simplified payment sum official accounting revenue

Payment receipts and recognized revenue are not always equivalent. A real financial report must follow the business's accounting definitions and refund, tax, and currency policies.

Correctly Aggregate Before Joining Payments

Correctly Aggregate Before Joining Payments
sql
-- First calculate one merchandise total per order.
WITH order_totals AS (
    SELECT
        o.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders AS o
    JOIN order_items AS oi
        ON oi.order_id = o.order_id
    GROUP BY o.order_id
),
successful_payments AS (
    SELECT
        order_id,
        SUM(amount) AS successful_payment_total
    FROM payments
    WHERE status = 'succeeded'
    GROUP BY order_id
)
SELECT
    ot.order_id,
    ot.order_total,
    COALESCE(sp.successful_payment_total, 0)
        AS successful_payment_total
FROM order_totals AS ot
LEFT JOIN successful_payments AS sp
    ON sp.order_id = ot.order_id
ORDER BY ot.order_id;
Output
order_id | order_total | successful_payment_total ---------+-------------+------------------------- 1 | 4900.00 | 4900.00 2 | 2800.00 | 2800.00 3 | 4200.00 | 4200.00 4 | 1200.00 | 0.00 5 | 1500.00 | 0.00

Each CTE produces at most one row per order before the results are joined. This avoids multiplying order-item rows by payment rows. The report compares merchandise totals with successful payment totals; it does not by itself reconcile refunds or prove accounting revenue.

Project Completion Checklist

You have built the foundation of an e-commerce database and reporting system.

Before marking the project complete, make sure you can:
- Explain the purpose of every table and relationship.
- Identify primary keys, foreign keys, unique constraints, and check constraints.
- Explain why historical unit prices belong in order_items.
- Calculate order totals and monthly successful payment totals.
- Rank products by units ordered.
- Find inactive customers and customers without successful payments.
- Explain how LEFT JOIN preserves unmatched records.
- Prevent negative inventory with a conditional update.
- Describe how transactions protect related database changes.
- Explain why multi-table aggregation can duplicate totals.
- Use indexes and execution plans to investigate performance.
- Identify the additional design needed for refunds, partial payments, warehouses, reservations, discounts, taxes, and multiple currencies.

This is a learning project, not a complete production commerce platform. A production design also needs carefully defined state transitions, authorization, auditability, idempotent payment processing, refund handling, and operational monitoring.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Calculating historical order totals using the current product price.

Why it happens: Developers may join products and order_items but use products.current_price instead of the price charged when the order was placed.

Incorrect Approach
sql
sql
SELECT
    oi.order_id,
    SUM(oi.quantity * p.current_price) AS total
FROM order_items AS oi
JOIN products AS p
    ON p.product_id = oi.product_id
GROUP BY oi.order_id;
Correct Solution
sql
sql
SELECT
    order_id,
    SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id;

Key takeaway: The order item stores the historical unit price. Use it when calculating original order totals, even if the current product price has changed.

#2 Decrementing stock after a separate check without making the check atomic.

Why it happens: Concurrent requests can both read the same stock value and each decide that enough units are available.

Incorrect Approach
sql
sql
-- Unsafe pattern when implemented as separate operations:
-- 1. Read stock_quantity.
-- 2. Check stock in application code.
-- 3. Decrement stock unconditionally.
Correct Solution
sql
sql
UPDATE inventory
SET
    stock_quantity = stock_quantity - 2,
    updated_at = CURRENT_TIMESTAMP
WHERE product_id = 1
  AND stock_quantity >= 2
RETURNING product_id, stock_quantity;

Key takeaway: The conditional update performs the check and decrement as one database operation. The application must check whether a row was returned and coordinate the update with order creation in a transaction.

#3 Counting every order as revenue, regardless of payment status.

Why it happens: An order record indicates a purchase workflow, not necessarily a successful payment.

Incorrect Approach
sql
sql
SELECT
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(*) AS orders,
    SUM(order_total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
Correct Solution
sql
sql
SELECT
    DATE_TRUNC('month', payment_date)::date AS payment_month,
    SUM(amount) AS successful_payment_total
FROM payments
WHERE status = 'succeeded'
GROUP BY DATE_TRUNC('month', payment_date)::date
ORDER BY payment_month;

Key takeaway: The corrected query calculates the project's defined successful-payment metric. A complete accounting revenue report needs further policies for refunds, chargebacks, taxes, and revenue recognition.

#4 Joining order items and payments before aggregation and accidentally multiplying totals.

Why it happens: Both tables can contain several rows per order. A direct join can pair every item with every payment for the same order.

Incorrect Approach
sql
sql
SELECT
    o.order_id,
    SUM(oi.quantity * oi.unit_price) AS merchandise_total,
    SUM(p.amount) AS payment_total
FROM orders AS o
JOIN order_items AS oi
    ON oi.order_id = o.order_id
JOIN payments AS p
    ON p.order_id = o.order_id
GROUP BY o.order_id;
Correct Solution
sql
sql
-- Aggregate order items and payments separately,
-- then join the one-row-per-order results.
WITH item_totals AS (
    SELECT
        order_id,
        SUM(quantity * unit_price) AS merchandise_total
    FROM order_items
    GROUP BY order_id
),
payment_totals AS (
    SELECT
        order_id,
        SUM(amount) AS payment_total
    FROM payments
    WHERE status = 'succeeded'
    GROUP BY order_id
)
SELECT
    i.order_id,
    i.merchandise_total,
    COALESCE(p.payment_total, 0) AS payment_total
FROM item_totals AS i
LEFT JOIN payment_totals AS p
    ON p.order_id = i.order_id;

Key takeaway: Aggregating each one-to-many relationship first prevents row multiplication from distorting the results.

#5 Assuming a foreign key automatically creates an index on its referencing column.

Why it happens: Developers may confuse referential-integrity constraints with indexes used to speed up queries.

Incorrect Approach
sql
sql
-- This foreign key does not automatically create
-- an index on inventory.product_id beyond indexes
-- required by the table's primary key definition.
-- For other foreign-key columns, inspect the access patterns.
Correct Solution
sql
sql
CREATE INDEX idx_orders_customer_date
    ON orders(customer_id, order_date DESC);
 
CREATE INDEX idx_order_items_product
    ON order_items(product_id);

Key takeaway: PostgreSQL creates indexes for primary keys and unique constraints but does not automatically create an index on every foreign-key column. Add indexes when the workload benefits from them.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

  • An e-commerce database separates customers, products, orders, order items, inventory, and payments into related tables.
  • Order items should store the price charged at the time of purchase so historical reports remain accurate after product prices change.
  • Inventory changes should be protected by database constraints and transactional updates.
  • Order status and payment status represent different business processes and should not be treated as interchangeable.
  • Revenue reports must define which order and payment statuses count as recognized revenue.
  • Indexes should be chosen for actual query patterns and validated using realistic data and execution plans.
Was this lesson helpful?
Found an error? Report an issue