Setting up a practice database

The practice schema

Every example in this course runs against the same database — an e-commerce platform called ShopMetrics. It has enough realism to make the queries meaningful, and enough simplicity that you're not fighting the data model.

Here's the schema:

-- Customers
CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,
    name          TEXT NOT NULL,
    email         TEXT UNIQUE NOT NULL,
    country       TEXT NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Products
CREATE TABLE products (
    product_id    SERIAL PRIMARY KEY,
    name          TEXT NOT NULL,
    category      TEXT NOT NULL,
    price         NUMERIC(10,2) NOT NULL
);

-- Orders
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INT REFERENCES customers,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    status        TEXT NOT NULL  -- 'completed', 'refunded', 'pending'
);

-- Order line items
CREATE TABLE order_items (
    item_id       SERIAL PRIMARY KEY,
    order_id      INT REFERENCES orders,
    product_id    INT REFERENCES products,
    quantity      INT NOT NULL,
    unit_price    NUMERIC(10,2) NOT NULL
);

-- Page events (for funnel analysis later)
CREATE TABLE events (
    event_id      SERIAL PRIMARY KEY,
    customer_id   INT REFERENCES customers,
    event_type    TEXT NOT NULL,  -- 'view', 'add_to_cart', 'purchase'
    page          TEXT,
    occurred_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);