SQL models by industry

These examples use SQL for orders, account entries, inventory and events. Each shows a table design and a useful question to ask it. Create the example tables before running the queries; they are separate from the quickstart product table.

Retail: an order bookCopy Anchor Link

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

The tablesCopy Anchor Link

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 monthCopy Anchor Link

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, searchedCopy Anchor Link

Add the relevant search index before searching this catalog. Attached table indexes and explicit document/vector writes have different update paths. A SQL INSERT does not automatically generate an embedding. See writing and indexing.

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

Financial services: a ledgerCopy Anchor Link

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

The tablesCopy Anchor Link

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

Balance per accountCopy Anchor Link

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

Run the query with the intended database identity and verify its table grants, row policies and column masks. A WHERE clause is not a substitute for database authorization. Test both permitted and denied records.

Logistics: inventory with reservationsCopy Anchor Link

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

The tablesCopy Anchor Link

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

What is actually availableCopy Anchor Link

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 streamCopy Anchor Link

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

The tablesCopy Anchor Link

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

Daily active usersCopy Anchor Link

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

If a dashboard repeatedly runs an expensive summary, consider saving it as a materialized view. That saved result needs an explicit refresh. See materialized views.

What these have in commonCopy Anchor Link

• The tables are the design. Every cheap question later comes from a key or a foreign key you declared now.

• Prepare each query method. Reuse source IDs across rows and search indexes, and keep each required representation current.

• 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.