E-commerce Tables and Indexes for Recent-Spend Queries

0) Problem Restatement

Visa asked a database design question: design normalized tables for an e-commerce system (users, products, orders, order line items) that preserve the price the customer actually paid, even if the product's price changes later, and enforce useful constraints. Then write and optimize queries such as "total spend per user in the last 30 days" and "top 10 customers by spend this month".

1) Tables

CREATE TABLE users (
  user_id     BIGINT PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  created_at  TIMESTAMP NOT NULL
);
CREATE TABLE products (
  product_id        BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  current_price_cents INT NOT NULL CHECK (current_price_cents >= 0)   -- can change any time
);
CREATE TABLE orders (
  order_id     BIGINT PRIMARY KEY,
  user_id      BIGINT NOT NULL REFERENCES users(user_id),
  status       TEXT NOT NULL CHECK (status IN ('placed','paid','shipped','cancelled','refunded')),
  total_cents  INT NOT NULL CHECK (total_cents >= 0),   -- sum of items at purchase time
  currency     CHAR(3) NOT NULL,
  created_at   TIMESTAMP NOT NULL
);
CREATE TABLE order_items (
  order_id          BIGINT REFERENCES orders(order_id),
  line_no           INT,
  product_id        BIGINT NOT NULL REFERENCES products(product_id),
  quantity          INT NOT NULL CHECK (quantity > 0),
  unit_price_cents  INT NOT NULL CHECK (unit_price_cents >= 0),   -- price CHARGED, copied at purchase
  PRIMARY KEY (order_id, line_no)
);

2) The Query

-- Total spend per user in the last 30 days (paid or shipped orders only)
SELECT o.user_id, SUM(o.total_cents) AS spend_cents
FROM orders o
WHERE o.created_at >= now() - INTERVAL '30 days'
  AND o.status IN ('paid', 'shipped')
GROUP BY o.user_id
ORDER BY spend_cents DESC
LIMIT 10;

-- One user's recent spend (e.g., for a profile page)
SELECT COALESCE(SUM(total_cents), 0) FROM orders
WHERE user_id = $1 AND created_at >= now() - INTERVAL '30 days' AND status IN ('paid','shipped');

3) Indexes

• For one user's recent spend: a composite index (user_id, created_at). The DB jumps to that user and reads only the last 30 days. Make it covering by including status, total_cents (CREATE INDEX ... ON orders (user_id, created_at) INCLUDE (status, total_cents)), so it never touches the table.
• For top spenders across all users in a time range: an index on (created_at) including (user_id, status, total_cents), so the scan reads only recent rows. For very large tables, partition orders by month so old partitions are skipped entirely (partition pruning).
• Always check with EXPLAIN ANALYZE: we want an index or index-only scan, not a sequential scan of the whole orders table.

flowchart LR
    Q["Query: user 42, last 30 days"] --> IX["Index (user_id, created_at) INCLUDE (status, total_cents)"]
    IX --> R["Read only matching index entries"]
    R --> SUM["SUM total_cents"]

4) Extras

• Refunds: store refunds as separate rows (or negative adjustments) with their own dates, so spend = charges − refunds in the window.
• Currency: store the order currency, and convert with the rate at purchase time for reporting in one currency.
• Dashboards at scale: a daily summary table user_daily_spend(user_id, day, spend_cents), updated incrementally. Then 30-day spend = the sum of ≤ 30 small rows.
• Why total_cents on orders? It's a small, safe denormalization that avoids joining order_items for spend queries. Keep it consistent by computing it in the same transaction as the items.

5) Wrap-Up

Use normalized users, products, orders and order_items tables with foreign keys and CHECK constraints, and copy the charged unit price into order_items (plus an order total) so history never changes when product prices do. Answer recent-spend queries from orders with a composite covering index on (user_id, created_at) (and a created_at index or monthly partitions for all-user rankings), verify with EXPLAIN, and add refund handling and daily summary tables as data grows.