sql · by industry

SQL models by industry

Four models teams actually build: an order book, a financial ledger, inventory with reservations and an event stream. Each starts with the tables, because the shape of the tables decides which questions stay cheap.

Retail: an order book

Orders, their lines, and the products they point at. The reporting questions are all aggregates over a join.

The tables

shop.products (id PK, name, brand, price)
shop.orders   (id PK, customer_id, status, placed_at)
shop.lines    (id PK, order_id -> orders.id, product_id -> products.id, qty)

Revenue by brand, last month

One statement, joined and grouped in the engine, rather than three round trips and a loop in your service.

SELECT p.brand,
       SUM(l.qty * p.price) AS revenue,
       COUNT(DISTINCT o.id) AS orders
FROM shop.lines l
JOIN shop.orders   o ON l.order_id = o.id
JOIN shop.products p ON l.product_id = p.id
WHERE o.status = 'paid'
GROUP BY p.brand
ORDER BY revenue DESC

The same catalog, searched

The rows you just queried are the rows full-text and vector search see. Nothing is copied, so a product added by an INSERT is searchable immediately — see the search quickstart.

SELECT id, name FROM shop.products WHERE brand = 'Aero' ORDER BY price ASC

Financial services: a ledger

Append-only entries, balances derived rather than stored, and money kept in minor units so totals never drift.

The tables

fin.accounts (id PK, holder, currency, opened_at)
fin.entries  (id PK, account_id -> accounts.id, amount_minor, kind, booked_at)

Balance per account

Derived from the entries, so it cannot disagree with them.

SELECT a.id, a.holder, a.currency,
       SUM(e.amount_minor) AS balance_minor
FROM fin.accounts a
JOIN fin.entries  e ON e.account_id = a.id
GROUP BY a.id, a.holder, a.currency

who sees which rows

Row-level security and column masking are enforced by the engine, not by your query. A caller scoped to one desk gets that desk's rows from this exact statement, and a masked column stays masked in an aggregate. You do not add a filter for it, and you cannot forget to.

Logistics: inventory with reservations

Stock on hand, minus what is promised. The trap is reading availability and acting on it after someone else already did.

The tables

wh.stock        (sku PK, on_hand)
wh.reservations (id PK, sku -> stock.sku, qty, state, made_at)

What is actually available

Subtract live reservations from stock in one statement.

SELECT s.sku,
       s.on_hand,
       COALESCE(SUM(r.qty), 0) AS reserved,
       s.on_hand - COALESCE(SUM(r.qty), 0) AS available
FROM wh.stock s
LEFT JOIN wh.reservations r
       ON r.sku = s.sku AND r.state = 'held'
GROUP BY s.sku, s.on_hand

make the decision atomic

Reading availability and then reserving is two steps, and another writer fits between them. Do the read and the write in one transaction — see transactions — so the reservation either wins or fails cleanly.

Product analytics: an event stream

High write volume, and questions that are almost always time-bucketed.

The tables

app.events (id PK, user_id, name, props_json, at)

Daily active users

Grouped in the engine over the whole table, not sampled in a job.

SELECT DATE_TRUNC('day', at) AS day,
       COUNT(DISTINCT user_id) AS dau
FROM app.events
WHERE at >= '2026-09-01T00:00:00Z'
GROUP BY day
ORDER BY day

when a query becomes a dashboard

A query a dashboard runs every minute is a materialized view. Declare it once and read it as a table — see materialized views.

What these have in common

The tables are the design. Every cheap question later comes from a key or a foreign key you declared now.
One store. These same rows answer full-text, vector, graph and natural-language queries. Nothing is exported to a warehouse to be asked a question.
Policy is enforced below your query. Row-level security and masking apply to the statement itself, including inside aggregates.

Start from zero

Schema, insert, select, join, aggregate.

More examples

Single-purpose recipes with their responses.