A support agent opens a customer profile and needs two things: the person’s current details and their past purchases. Keeping those facts in separate tables avoids copying the customer’s name into every order. A join connects the facts when the application needs them.

This guide builds a small, synthetic order-history dataset. You will distinguish a purchase-level result from a customer-level summary, inspect a customer with no purchases and check a filter that accidentally removes that customer. The examples are SQL authoring examples based on the documented interface; verify them against your deployed version before adapting them to production.

1. Define what one row represents

The customer table contains one row per customer. The order table contains one row per purchase. A joined history normally contains one row per matching purchase, so a customer with two orders appears twice. That repetition is expected; it is not automatically a duplicate-data error.

Decide the shape of your screen first. A timeline wants purchase rows. A customer directory wants customer rows, perhaps with an aggregate count. Mixing those two grains is a common source of incorrect totals.

Use a fresh demo namespace, a running OriginChainDB instance and an account permitted to create and query demo tables. Open the SQL Workbench. Keep credentials outside the examples and never use real customer information for a public demonstration.

2. Create checkable input

Run each statement separately and inspect its result:

CREATE SCHEMA history_demo_20261009;

CREATE TABLE history_demo_20261009.customers (
  id TEXT PRIMARY KEY, name TEXT
);
CREATE TABLE history_demo_20261009.orders (
  id TEXT PRIMARY KEY, customer_id TEXT, amount_cents BIGINT, status TEXT
);
INSERT INTO history_demo_20261009.customers (id,name)
VALUES ('c1','Asha'),('c2','Maya'),('c3','Noor');
INSERT INTO history_demo_20261009.orders (id,customer_id,amount_cents,status)
VALUES ('o1','c1',1200,'paid'),('o2','c1',800,'pending'),('o3','c2',500,'paid');

The amounts use integer cents for this deliberately simple example. A real application must also define currency, scale and rounding behavior. The customer reference here is a stored value; this SQL example does not establish a foreign-key constraint.

Read both tables ordered by ID. Expect three customers and three purchases. Asha has two orders, Maya has one and Noor has none. Write those expectations down before running the join.

3. Build the purchase history

SELECT c.id AS customer_id, c.name, o.id AS order_id,
       o.amount_cents, o.status
FROM history_demo_20261009.customers c
JOIN history_demo_20261009.orders o ON o.customer_id = c.id
ORDER BY customer_id, order_id;

Expect three result rows: c1/o1, c1/o2 and c2/o3. Noor is absent because an inner join returns matching pairs. Adding a new order for Asha should add another history row, not another customer identity.

For a customer-specific screen, apply the selected customer’s identifier as a parameter through the supported client interface. Do not build a production query by concatenating an untrusted identifier into SQL text.

4. Preserve customers with no orders

SELECT c.id AS customer_id, c.name, o.id AS order_id
FROM history_demo_20261009.customers c
LEFT JOIN history_demo_20261009.orders o ON o.customer_id = c.id
ORDER BY customer_id, order_id;

This result should include Noor with missing order-side values. Your interface can display “No orders yet” instead of inventing an order with amount zero. Those are different statements: one means no purchase exists; the other means a purchase exists at a known zero amount.

When adding a status filter, ask what it applies to. A condition on the order side in WHERE can remove the unmatched row. If the intended question is “every customer and their paid orders, if any,” place the order-status condition in the join condition and verify the behavior with Noor’s row.

5. Count orders without counting the placeholder

SELECT c.id AS customer_id, c.name, COUNT(o.id) AS order_count
FROM history_demo_20261009.customers c
LEFT JOIN history_demo_20261009.orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY customer_id;

The intended counts are 2, 1 and 0. Counting the order ID matters because the unmatched customer row has no order ID. Compare that with COUNT(*) on your test dataset so you can explain the difference rather than memorizing syntax.

6. Make the report an application contract

Keep the three-row customer fixture as a regression test. Add a case where the customer’s display name changes, a purchase has an unexpected status and an order references a missing customer. Decide whether the application should reject, quarantine or report that last case.

Save the verified query in the Workbench with a name that states its grain, such as “Customer order counts, including zero.” Document the expected order of results. If you later add pagination, use a deterministic sort and consider what happens when new purchases arrive between requests.

You now have two useful outputs from the same source model: a purchase history and a customer summary. The key skill is choosing what one output row means, then checking the join and aggregate against that promise.

Continue with the OriginChainDB SQL documentation.

Make the paid-order filter concrete

The fixture can demonstrate the difference between a row filter and a join condition without adding more data. To preserve every customer while attaching only paid orders, run:

SELECT c.id AS customer_id, c.name, o.id AS order_id, o.status
FROM history_demo_20261009.customers c
LEFT JOIN history_demo_20261009.orders o
  ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY customer_id, order_id;

The expected pairs are c1/o1, c2/o3 and c3 with no order-side values. Asha’s pending order o2 is excluded from this view, but Noor remains. Now move the paid-status condition into a WHERE clause and compare the result. Noor disappears because the unmatched order side cannot satisfy o.status = 'paid'.

That is not a database inconsistency. The two statements ask different questions. The first asks for all customers and any paid matches; the second asks for joined rows whose order status is paid. Translate the product requirement into a sentence before choosing the SQL form, then keep the empty-customer fixture in your tests.

Keep customer totals and order totals apart

If you total the amount column across the original joined history, you are summing order amounts. If you count the joined rows, you are counting orders, not distinct customers. The fixture’s three history rows represent only two customers with purchases. A customer directory built from those rows can accidentally show Asha twice unless it deliberately groups to the customer grain.

For an amount summary, decide whether pending orders belong in the total. “Paid value” and “all order value” are different metrics. Record currency and numeric scale before combining real amounts; adding values from two currencies is not made valid by storing both as integers. This small fixture uses one illustrative cents scale solely to keep the arithmetic checkable.

An orphaned order is a separate quality problem. In a disposable copy, add an order with an unknown customer ID and confirm that the customer-led inner join does not expose it. A report that starts from customers can miss orphaned orders entirely. Build a reconciliation check from the order side rather than using a successful history screen as proof that every order has a valid reference.

Design pagination around a stable ordering contract

The sample orders are sorted by ID so repeated runs have a deterministic order. A production history usually sorts by purchase time, with the unique order ID as a tie-breaker. Two purchases can share a timestamp; ordering only by that timestamp leaves their relative position unspecified.

Consider what happens if a new order arrives between page one and page two. Offset pagination and cursor-style pagination have different behaviors under inserts. Choose a query and client protocol supported by the deployment, then test overlapping timestamps, insertion between requests and an empty final page. Do not add a cursor parameter to the UI without defining exactly which ordered values it encodes.

Deliver a clear empty state and a useful error state

Noor’s history should say that no orders are recorded, while an unavailable query should say that the history could not be loaded. A failed read should not become an empty purchase history. Similarly, a missing customer identity differs from an existing customer with no purchases.

Before shipping, keep a small table of expected outcomes: original inner join has three rows, left join has four rows, counts are 2/1/0, and the paid-only left join retains three customer rows. These checks explain the screen’s semantics to a reviewer. They also give future changes to the query an immediate, understandable regression test.