Graphs & referrals

This tutorial uses recursion to answer questions that cannot be expressed as a fixed number of parent-child joins.

You will measure referral depth, roll revenue up to an organic root, audit revenue lost through a null grouping key, and label connected components in a separate edge list.

This advanced tutorial assumes entity bindings and RELATED() from E-commerce . RECURSE() is the new idea: start from a seed row, follow an edge repeatedly, and stop at MAX_LEVEL. Run Data and Model before the graph queries.

Meet the referral forest

There are two organic roots. Alpha starts the larger referral tree, while Golf starts a second one:

Alpha (organic)
├── Bravo
│   └── Delta
│       └── Echo   ← segment NULL
└── Charlie
Golf (organic)
└── Hotel

null

FeatureQL

CREATE SCHEMA IF NOT EXISTS tutorial_graph;
--
DROP TABLE IF EXISTS tutorial_graph.revenue;
--
DROP TABLE IF EXISTS tutorial_graph.customers;
--
CREATE TABLE tutorial_graph.customers (
  id BIGINT,
  name VARCHAR,
  segment VARCHAR,
  referred_by BIGINT
);
--
INSERT INTO tutorial_graph.customers VALUES
  (1, 'Alpha',   'enterprise', NULL),
  (2, 'Bravo',   'smb',        1),
  (3, 'Charlie', 'smb',        1),
  (4, 'Delta',   'mid_market', 2),
  (5, 'Echo',    NULL,         4),
  (6, 'Golf',    'enterprise', NULL),
  (7, 'Hotel',   'smb',        6);
--
CREATE TABLE tutorial_graph.revenue (
  customer_id BIGINT,
  amount BIGINT
);
--
INSERT INTO tutorial_graph.revenue VALUES
  (1, 5000),
  (2, 3000),
  (3, 4500),
  (4, 2000),
  (5,  800),
  (6, 6000),
  (7, 3500);
--
SELECT CAST(COUNT() AS INTEGER) AS cnt FROM tutorial_graph.customers;

Result

Count BIGINT
7

Echo's segment is null. That does not affect the referral edges, but it creates a useful audit case when revenue is grouped by segment.

Model parent links

Declare customers and revenue records as entities:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.GRAPH AS
PERSIST
    customers := ENTITY(),
    customer_id := INPUT(BIGINT#customers)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.GRAPH.CUSTOMERS	CREATED	Feature created as not exists
FM.GRAPH.CUSTOMER_ID	CREATED	Feature created as not exists

Then map each customer's optional referred_by parent and one revenue amount per customer:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.GRAPH AS
PERSIST
    tables.customers := EXTERNAL_COLUMNS(
        id BIGINT#customers BIND TO customer_id,
        name VARCHAR,
        segment VARCHAR,
        referred_by BIGINT
        FROM TABLE(tutorial_graph.customers)
    ),
    customer_name := tables.customers[name],
    ref_by := tables.customers[referred_by],
    segment := tables.customers[segment],
    tables.customer_revenue := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        amount BIGINT
        FROM TABLE(tutorial_graph.revenue)
    ),
    rev_amt := tables.customer_revenue[amount]
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.GRAPH.TABLES.CUSTOMERS	CREATED	Feature created as not exists
FM.GRAPH.CUSTOMER_NAME	CREATED	Feature created as not exists
FM.GRAPH.REF_BY	CREATED	Feature created as not exists
FM.GRAPH.SEGMENT	CREATED	Feature created as not exists
FM.GRAPH.TABLES.CUSTOMER_REVENUE	CREATED	Feature created as not exists
FM.GRAPH.REV_AMT	CREATED	Feature created as not exists

A null referred_by means the customer is organic. Every non-null value points to another customer in the same entity.

Count hops from the organic root

For each customer, follow referred_by repeatedly until the organic root. The depth is the number of referral edges, with the root itself at depth zero.

FeatureQL

DEFINE
    cust_id := tables.customers[id],
    walk := ROW(customer_id AS start).RECURSE(
        RETURN node_id := step[cust_id]
        VIA start BIND TO cust_id
        FOLLOW ref_by
        MAX_LEVEL 10
    ),
    ref_depth := COALESCE(walk.TRANSFORM(RETURN MAX(level)).UNWRAP_ONE(), 0) - 1
RETURN
    customer_name,
    ref_depth
FROM FM.GRAPH
FOR
    customer_id := BIND_VALUES(ARRAY(1, 2, 3, 4, 5, 6, 7))
ORDER BY customer_name
;

Result

CUSTOMER_NAME VARCHAR	REF_DEPTH BIGINT
Alpha	0
Bravo	1
Charlie	1
Delta	2
Echo	3
Golf	0
Hotel	1

Alpha and Golf have depth 0. Bravo, Charlie, and Hotel have depth 1; Delta has 2; Echo has 3.

A fixed self-join could handle a known maximum depth. Recursion handles the same rule without deciding in advance whether the chain has two, three, or more levels.

Roll revenue up to each root

Run the parent walk again, but keep the final organic root for every customer. Group revenue by that root and include the root's own revenue:

FeatureQL

DEFINE
    cust_id := tables.customers[id],
    walk := ROW(customer_id AS start).RECURSE(
        RETURN node_id := step[cust_id]
        VIA start BIND TO cust_id
        FOLLOW ref_by
        MAX_LEVEL 10
    ),
    organic_root := KEY_TO_SCALAR(
        COALESCE(
            walk.TRANSFORM(
                RETURN node_id
                ORDER BY level DESC
                LIMIT 1
            ).UNWRAP_ONE(),
            customer_id
        )
    ),
    tree_rev := SUM(rev_amt) GROUP BY (organic_root)
RETURN
    root_id := organic_root,
    tree_rev
FROM FM.GRAPH
FOR
    NESTED customer_id := BIND_VALUES(ARRAY(1, 2, 3, 4, 5, 6, 7))
ORDER BY root_id
;

Result

ROOT_ID BIGINT	TREE_REV BIGINT
1	15300
6	9500

Alpha's tree contributes 15300. Golf's tree contributes 9500.

The aggregation happens after each customer has been labeled with a root. That makes the recursive step and the revenue rollup separate pieces of logic.

Audit revenue dropped by a null dimension

Grouping by a nullable dimension can silently leave revenue outside the reported categories. Compare total revenue with revenue whose segment is not null:

FeatureQL

DEFINE
    total_rev := SUM(rev_amt),
    parts_sum := SUM(rev_amt) FILTER (WHERE segment IS NOT NULL),
    delta := total_rev - parts_sum
RETURN
    total_rev,
    parts_sum,
    delta
FROM FM.GRAPH
FOR
    customer_id := BIND_VALUES(ARRAY(1, 2, 3, 4, 5, 6, 7))
;

Result

TOTAL_REV BIGINT	PARTS_SUM BIGINT	DELTA BIGINT
24800	24000	800

Total revenue is 24800, while rows with a known segment sum to 24000. The 800 gap is Echo.

This check is deliberately simple: when category totals are expected to reconcile, calculate and expose the difference instead of assuming nulls are harmless.

Label connected components

The final example uses a separate, small undirected edge list. Two nodes belong to the same component when a path connects them, even if they do not share an edge directly.

Seed a traversal from every node, recurse across edges, and use the smallest reachable ID as the component label:

FeatureQL

DEFINE
    edges_e := ENTITY(),
    edge_id := INPUT(BIGINT#edges_e),
    edge_table := INLINE_COLUMNS(
        edge_id BIGINT#edges_e BIND TO edge_id,
        edge_src BIGINT#customers,
        edge_dst BIGINT#customers
        FROM CSV(
            edge_id,edge_src,edge_dst
            1,1,2
            2,2,1
            3,2,4
            4,4,2
            5,6,7
            6,7,6
        )
    ),
    edge_src := edge_table[edge_src],
    edge_dst := edge_table[edge_dst],
    reach := ROW(customer_id AS start).RECURSE(
        RETURN peer := step[edge_dst]
        VIA start BIND TO edge_src
        FOLLOW edge_dst
        MAX_LEVEL 10
    ),
    component_id := CARRY(customer_id AS seed INTO reach).TRANSFORM(
        RETURN component_id := KEY_TO_SCALAR(MIN(COALESCE(peer, seed)))
    ).UNWRAP_ONE()
RETURN
    customer_name,
    component_id
FROM FM.GRAPH
FOR
    customer_id := BIND_VALUES(ARRAY(1, 2, 3, 4, 5, 6, 7)),
    NESTED edge_id := BIND_VALUES(ARRAY(1, 2, 3, 4, 5, 6))
ORDER BY customer_name
;

Result

CUSTOMER_NAME VARCHAR	COMPONENT_ID BIGINT
Alpha	1
Bravo	1
Charlie	3
Delta	1
Echo	5
Golf	6
Hotel	6

Alpha, Bravo, and Delta receive component 1. Charlie remains component 3. Echo has no edges and remains 5. Golf and Hotel share component 6.

Unlike the referral examples, this graph has no designated parent or root. The minimum reachable ID gives each connected set a stable label.

What's next

You have used recursion for a rooted hierarchy and an unrooted graph, then kept aggregation and data-quality checks explicit around the traversal.

Continue with OBT modeling for complex analysis inside nested arrays, or return to the analytics overview to compare this technique with non-recursive rollups.

Last update at: 2026/10/13 08:32:21

On this page

Meet the referral forest Model parent links Count hops from the organic root Roll revenue up to each root Audit revenue dropped by a null dimension Label connected components What's next