Graphs & referrals

Walk a customer referral tree with RECURSE(): depth from organic roots, revenue for each subtree, a NULL-segment revenue audit, and connected components from a short edge list — no separate graph database required.

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

Data

Two organic roots. Alpha refers Bravo and Charlie; Bravo refers Delta; Delta refers Echo (segment NULL, so segment rollups miss that revenue). Golf refers Hotel. The connected-components query inlines a tiny undirected edge list.

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

null

FeatureQL

/* SQL */
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

Model

Customers carry referred_by (NULL = organic). Revenue is one amount per customer.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.GRAPH AS
SELECT
    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

FeatureQL

CREATE OR REPLACE FEATURES IN FM.GRAPH AS
SELECT
    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

Referral depth

From each customer, walk parent links until the organic root. Depth = hops from organic (organic itself is 0).

FeatureQL

WITH
    CUST_ID := TABLES.CUSTOMERS[id],
    WALK := ROW(CUSTOMER_ID AS start).RECURSE(
        SELECT step[cust_id] AS node_id
        VIA start BIND TO cust_id
        FOLLOW ref_by
        MAX_LEVEL 10
    ),
    REF_DEPTH := COALESCE(WALK.TRANSFORM(SELECT MAX(level)).UNWRAP_ONE(), 0) - 1,
SELECT
    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/Golf 0, Bravo/Charlie/Hotel 1, Delta 2, Echo 3.

Subtree revenue

Same walk, but keep the organic root for every node, then SUM revenue by root (includes the root’s own revenue).

FeatureQL

WITH
    cust_id := tables.customers[id],
    walk := ROW(customer_id AS start).RECURSE(
        SELECT node_id := step[cust_id]
        VIA start BIND TO cust_id
        FOLLOW ref_by
        MAX_LEVEL 10
    ),
    organic_root := COALESCE(
        walk.TRANSFORM(SELECT NODE_ID ORDER BY LEVEL DESC LIMIT 1).UNWRAP_ONE(),
        customer_id
    ),
    tree_rev := SUM(rev_amt) GROUP BY organic_root
SELECT
    root_id := organic_root,
    tree_rev
FROM FM.GRAPH
FOR
    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 tree 15300. Golf tree 9500.

NULL segment audit

Total revenue vs sum of revenue where segment IS NOT NULL. The gap is Echo’s 800.

FeatureQL

WITH
    total_rev := SUM(rev_amt),
    parts_sum := SUM(rev_amt) FILTER (WHERE segment IS NOT NULL),
    delta := total_rev - parts_sum
SELECT
    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

24800 − 24000 = 800. Group-by dimensions silently drop NULLs — check the delta.

Connected components

Customers that share an edge are in the same component (transitive). Seed each node, RECURSE across edges, take MIN reachable id as the component label.

FeatureQL

WITH
    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(
        SELECT step[edge_dst] AS peer
        VIA start BIND TO edge_src
        FOLLOW edge_dst
        MAX_LEVEL 10
    ),
    COMPONENT_ID := REACH.CARRY(CUSTOMER_ID AS seed).TRANSFORM(
        SELECT MIN(COALESCE(peer, seed)) AS component_id
    ).UNWRAP_ONE(),
SELECT
    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/Delta → 1. Charlie → 3. Golf/Hotel → 6. Echo has no edges → 5.

What's next

Analytics overview — concept map for this series
OBT modeling — array-of-rows, nested argmax, profile ROWs
Temporal & experiments — SCD as-of and bi-temporal cutoffs
Product analytics — funnels on event logs
Financial consolidation — hierarchy-style rollups without graphs

Last update at: 2026/08/04 11:25:38

On this page

Data Model Referral depth Subtree revenue NULL segment audit Connected components What's next