E-commerce

This tutorial builds a small retail feature model and uses it to answer questions that cross customers, orders, and line items.

The business questions are simple—How many orders did a customer place? Where was their latest order? What was it worth?—but each follows a different relationship shape. Learning those shapes here will make the rest of the analytics tutorials easier to read.

No previous domain tutorial is required. If ENTITY(), mappings, or FOR are completely new, the shorter FeatureQL homepage companion is a useful warm-up.

Run Data and Model before the relationship examples. If a notebook restarts, rerun those sections before continuing.

Meet the retail data

We have three customers, four orders, and seven line items. John has two orders, while Jane and Jack have one each.

The setup also includes two small pre-aggregated helpers: one stores each customer's order IDs and a designated last_order_id, and another stores each order's total price. They keep the later array example focused. In production, you could derive or materialize the same values upstream.

SELECT customer_id, name
FROM tutorial_ecomm.customers
ORDER BY customer_id;

After running the cell, inspect customer 100: that is John, whose orders 200 and 203 give us a useful one-to-many case. The helper stores order 200 as John's last_order_id; this tutorial follows that stored field rather than deriving the latest order from timestamps.

Give the tables business meaning

First declare customers, orders, and items as distinct entities. Their keys are all integers in storage, but the entity annotations stop a customer ID from being mistaken for an order ID.

CREATE OR REPLACE FEATURES IN FM.ECOMM AS
PERSIST
    customers := ENTITY(),
    orders := ENTITY(),
    items := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    order_id := INPUT(BIGINT#orders),
    item_id := INPUT(BIGINT#items)
;

Now map the physical columns. EXTERNAL_COLUMNS() records the source table, column types, and key used to bind each row.

CREATE OR REPLACE FEATURES IN FM.ECOMM AS
PERSIST
    tables.customers := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        name VARCHAR,
        time_create TIMESTAMP
        FROM TABLE(tutorial_ecomm.customers)
    ),
    tables.orders := EXTERNAL_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        order_customer_id BIGINT#customers,
        order_city_name VARCHAR,
        time_create TIMESTAMP
        FROM TABLE(tutorial_ecomm.orders)
    ),
    tables.items := EXTERNAL_COLUMNS(
        item_id BIGINT#items BIND TO item_id,
        item_order_id BIGINT#orders,
        item_product_name VARCHAR,
        price DECIMAL(10,2),
        quantity BIGINT
        FROM TABLE(tutorial_ecomm.items)
    ),
    tables.customer_orders := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        last_order_id BIGINT#orders,
        orders ARRAY(BIGINT#orders)
        FROM TABLE(tutorial_ecomm.customer_orders)
    ),
    tables.order_totals := EXTERNAL_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        price DECIMAL(10,2)
        FROM TABLE(tutorial_ecomm.order_totals)
    )
;

The persisted TABLES.* features represent raw sources. The shorter names—such as customer_name, order_city, and item_price—are the stable business features the queries will compose.

See what a binding selects

Before crossing relationships, look at each population separately. Binding customer IDs produces one row per customer:

RETURN
    customer_id,
    name := tables.customers[name],
    time_create := tables.customers[time_create]
FROM FM.ECOMM
FOR
    customer_id := BIND_COLUMNS(
        customer_id
        FROM TABLE(tutorial_ecomm.customers)
    )
ORDER BY customer_id
;

Binding order IDs changes the output grain to one row per order:

RETURN
    order_id,
    order_customer_id := tables.orders[order_customer_id],
    city := tables.orders[order_city_name],
    time_create := tables.orders[time_create]
FROM FM.ECOMM
FOR
    order_id := BIND_COLUMNS(
        order_id
        FROM TABLE(tutorial_ecomm.orders)
    )
ORDER BY order_id
;

FOR therefore does more than supply values: it tells you which business objects the query is evaluating. Later, NESTED bindings will make child keys available inside a relationship without multiplying these outer rows.

Align features on the same entity

Start at the customer grain and select two features already keyed by CUSTOMER_ID. Because they describe the same entity, FeatureQL aligns them directly; no RELATED() is needed.

RETURN
    customer_id,
    customer_name := tables.customers[name],
    customer_orders := tables.customer_orders[orders]
FROM FM.ECOMM
FOR
    customer_id := BIND_VALUES(ARRAY(100, 101, 102))
ORDER BY customer_id
;

John's row contains the order array [200, 203]. Jane and Jack each have a one-element array.

Aggregate child rows back to a customer

Now count orders for each customer. The result starts at the customer key, groups the child rows by their customer foreign key, and returns the count to the matching customer.

RETURN
    customer_id,
    num_orders := RELATED(
        SUM(1) GROUP BY (tables.orders[order_customer_id]) VIA customer_id
    )
FROM FM.ECOMM
FOR
    customer_id := BIND_VALUES(ARRAY(100, 101, 102)),
    NESTED order_id := BIND_VALUES(ARRAY(200, 201, 202, 203))
ORDER BY customer_id
;

The result is John 2, Jane 1, and Jack 1. In SQL, this shape usually requires a GROUP BY subquery followed by a join.

Follow a foreign key to one parent row

Each customer row stores a last_order_id. Follow that key to the order entity and retrieve its city:

DEFINE
    last_order_city := RELATED(
        tables.orders[order_city_name] VIA tables.customer_orders[last_order_id]
    )
RETURN
    customer_id,
    last_order_city
FROM FM.ECOMM
FOR
    customer_id := BIND_VALUES(ARRAY(100, 101, 102))
ORDER BY customer_id
;

All three designated last orders are in Barcelona. This is a lookup rather than an aggregation: one order ID identifies one order row.

Continue across another relationship

The next question travels farther. Begin with each customer's stored last_order_id, find the items that belong to that order, then sum price × quantity.

DEFINE
    last_order_id := tables.customer_orders[last_order_id],
    last_order_price := RELATED(
        SUM(tables.items[price] * tables.items[quantity]::DECIMAL)
        GROUP BY (tables.items[item_order_id]) VIA last_order_id
    )
RETURN
    customer_id,
    last_order_id,
    last_order_price
FROM FM.ECOMM
FOR
    customer_id := BIND_VALUES(ARRAY(100, 101, 102)),
    NESTED item_id := BIND_VALUES(ARRAY(300, 301, 302, 303, 304, 305, 306))
ORDER BY customer_id
;

John's stored last order 200 totals 31.15. Jane's totals 36.15, and Jack's totals 55.20. The output remains one row per customer even though the calculation reaches line items.

Enrich arrays with EXTEND()

So far, RELATED() has returned one scalar per customer. To keep several related rows, start with the customer's order-ID array:

1. ZIP() turns the scalar IDs into rows.
2. EXTEND() follows each order ID to its total price.
3. The enriched array stays inside the customer row.

RETURN
    customer_id,
    customer_orders := tables.customer_orders[orders],
    customer_orders_details := EXTEND(
        ZIP(customer_orders AS order_id)
        WITH tables.order_totals[price] AS order_price
    )
FROM FM.ECOMM
FOR
    customer_id := BIND_VALUES(ARRAY(100, 101, 102))
ORDER BY customer_id
;

John now carries two enriched rows: order 200 at 31.15 and order 203 at 16.05.

Compose the relationship shapes

The final query combines customer name, enriched orders, stored last-order city, order count, and stored last-order value. Read each selected feature as a separate business question that FeatureQL aligns at the customer grain.

DEFINE
    customer_name := tables.customers[name],
    customer_orders := tables.customer_orders[orders],
    last_order_id := tables.customer_orders[last_order_id],
    last_order_city := last_order_id.RELATED(tables.orders[order_city_name]),
    num_orders := ARRAY_COUNT(customer_orders),
    last_order_price := last_order_id.RELATED(
        SUM(tables.items[price] * tables.items[quantity]::DECIMAL)
        GROUP BY (tables.items[item_order_id])
    )
RETURN
    customer_id,
    customer_name,
    customer_orders,
    last_order_id,
    last_order_city,
    num_orders,
    last_order_price
FROM FM.ECOMM
FOR
    customer_id := BIND_VALUES(ARRAY(100, 101, 102)),
    NESTED item_id := BIND_VALUES(ARRAY(300, 301, 302, 303, 304, 305, 306))
ORDER BY customer_id
;

Notice the bindings: customers define the outer rows; orders and items are NESTED because the relationship calculations need their keys internally.

What's next

You have seen automatic same-entity alignment, child aggregation, parent lookup, a multi-hop calculation, and array enrichment.

Continue with SaaS metrics to reuse those relationships in point-in-time measures and a persisted health score. Use the RELATED() and EXTEND() reference pages when you need the complete syntax.