Support operations

Turn a small customer-support event log into queue, SLA, satisfaction, escalation, and agent-workload metrics while keeping each relationship explicit.

This tutorial uses a DuckDB dataset and assumes entities, mappings, and RELATED() . Run Data and Model before the eight metric queries. The examples deliberately keep customer, ticket, and agent keys separate: a FOR binding chooses the output grain, while @BIND_KEYSET(TABLES.<source>) derives each population directly from its EXTERNAL_COLUMNS() source. NESTED bindings make child keys available to relationship rewrites.

Data

Four customers, six tickets, eleven ticket events, four survey responses, and three agents. Atlas and Cobalt each have one open ticket; Beacon’s response took 150 minutes; only Atlas and Cobalt had escalations.

FeatureQL

/* SQL */
CREATE SCHEMA IF NOT EXISTS tutorial_support;
--
DROP TABLE IF EXISTS tutorial_support.surveys;
--
DROP TABLE IF EXISTS tutorial_support.events;
--
DROP TABLE IF EXISTS tutorial_support.tickets;
--
DROP TABLE IF EXISTS tutorial_support.agents;
--
DROP TABLE IF EXISTS tutorial_support.customers;
--
CREATE TABLE tutorial_support.customers (
  customer_id BIGINT,
  name VARCHAR,
  plan VARCHAR
);
--
INSERT INTO tutorial_support.customers VALUES
  (1, 'Atlas', 'pro'),
  (2, 'Beacon', 'free'),
  (3, 'Cobalt', 'enterprise'),
  (4, 'Delta', 'pro');
--
CREATE TABLE tutorial_support.agents (
  agent_id BIGINT,
  name VARCHAR,
  team VARCHAR
);
--
INSERT INTO tutorial_support.agents VALUES
  (10, 'Iris', 'billing'),
  (11, 'Jon', 'technical'),
  (12, 'Mina', 'technical');
--
CREATE TABLE tutorial_support.tickets (
  ticket_id BIGINT,
  customer_id BIGINT,
  priority VARCHAR,
  channel VARCHAR,
  status VARCHAR,
  created_at TIMESTAMP,
  first_response_at TIMESTAMP,
  resolved_at TIMESTAMP
);
--
INSERT INTO tutorial_support.tickets VALUES
  (101, 1, 'urgent', 'email', 'closed',
   TIMESTAMP '2024-04-01 09:00:00', TIMESTAMP '2024-04-01 09:15:00', TIMESTAMP '2024-04-01 13:00:00'),
  (102, 1, 'normal', 'chat', 'open',
   TIMESTAMP '2024-04-02 10:00:00', NULL, NULL),
  (201, 2, 'high', 'web', 'closed',
   TIMESTAMP '2024-04-03 11:00:00', TIMESTAMP '2024-04-04 13:30:00', TIMESTAMP '2024-04-05 10:00:00'),
  (301, 3, 'urgent', 'api', 'closed',
   TIMESTAMP '2024-04-04 08:00:00', TIMESTAMP '2024-04-04 08:05:00', TIMESTAMP '2024-04-04 09:00:00'),
  (302, 3, 'normal', 'email', 'open',
   TIMESTAMP '2024-04-05 14:00:00', NULL, NULL),
  (401, 4, 'high', 'chat', 'closed',
   TIMESTAMP '2024-04-06 09:00:00', TIMESTAMP '2024-04-06 09:30:00', TIMESTAMP '2024-04-07 11:00:00');
--
CREATE TABLE tutorial_support.events (
  event_id BIGINT,
  ticket_id BIGINT,
  customer_id BIGINT,
  agent_id BIGINT,
  event_type VARCHAR,
  ts TIMESTAMP
);
--
INSERT INTO tutorial_support.events VALUES
  (1, 101, 1, 10, 'opened', TIMESTAMP '2024-04-01 09:00:00'),
  (2, 101, 1, 10, 'resolved', TIMESTAMP '2024-04-01 13:00:00'),
  (3, 102, 1, 11, 'opened', TIMESTAMP '2024-04-02 10:00:00'),
  (4, 102, 1, 11, 'escalated', TIMESTAMP '2024-04-02 12:00:00'),
  (5, 201, 2, 10, 'opened', TIMESTAMP '2024-04-03 11:00:00'),
  (6, 201, 2, 10, 'resolved', TIMESTAMP '2024-04-05 10:00:00'),
  (7, 301, 3, 12, 'opened', TIMESTAMP '2024-04-04 08:00:00'),
  (8, 301, 3, 12, 'escalated', TIMESTAMP '2024-04-04 08:03:00'),
  (9, 302, 3, 11, 'opened', TIMESTAMP '2024-04-05 14:00:00'),
  (10, 401, 4, 12, 'opened', TIMESTAMP '2024-04-06 09:00:00'),
  (11, 401, 4, 12, 'resolved', TIMESTAMP '2024-04-07 11:00:00');
--
CREATE TABLE tutorial_support.surveys (
  survey_id BIGINT,
  ticket_id BIGINT,
  customer_id BIGINT,
  score BIGINT
);
--
INSERT INTO tutorial_support.surveys VALUES
  (1, 101, 1, 5),
  (2, 201, 2, 2),
  (3, 301, 3, 4),
  (4, 401, 4, 3);
--
SELECT CAST(COUNT(*) AS INTEGER) AS cnt FROM tutorial_support.tickets;

Result

Count BIGINT
6

Model

Declare the three business entities and their typed keys.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SUPPORT AS
SELECT
    CUSTOMERS := ENTITY(),
    TICKETS := ENTITY(),
    AGENTS := ENTITY(),
    EVENTS := ENTITY(),
    SURVEYS := ENTITY(),
    CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),
    TICKET_ID := INPUT(BIGINT#TICKETS),
    AGENT_ID := INPUT(BIGINT#AGENTS),
    EVENT_ID := INPUT(BIGINT#EVENTS),
    SURVEY_ID := INPUT(BIGINT#SURVEYS),
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.SUPPORT.CUSTOMERS	CREATED	Feature created as not exists
FM.SUPPORT.TICKETS	CREATED	Feature created as not exists
FM.SUPPORT.AGENTS	CREATED	Feature created as not exists
FM.SUPPORT.EVENTS	CREATED	Feature created as not exists
FM.SUPPORT.SURVEYS	CREATED	Feature created as not exists
FM.SUPPORT.CUSTOMER_ID	CREATED	Feature created as not exists
FM.SUPPORT.TICKET_ID	CREATED	Feature created as not exists
FM.SUPPORT.AGENT_ID	CREATED	Feature created as not exists
FM.SUPPORT.EVENT_ID	CREATED	Feature created as not exists
FM.SUPPORT.SURVEY_ID	CREATED	Feature created as not exists

Map physical columns once. A ticket row points to a customer, an event points to a ticket, customer, and agent, and a survey points to the customer and ticket.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SUPPORT AS
SELECT
    TABLES.CUSTOMERS := EXTERNAL_COLUMNS(
        customer_id BIGINT#CUSTOMERS BIND TO CUSTOMER_ID,
        name VARCHAR,
        plan VARCHAR,
        FROM TABLE(tutorial_support.customers)
    ),
    TABLES.TICKETS := EXTERNAL_COLUMNS(
        ticket_id BIGINT#TICKETS BIND TO TICKET_ID,
        customer_id BIGINT#CUSTOMERS,
        priority VARCHAR,
        channel VARCHAR,
        status VARCHAR,
        created_at TIMESTAMP,
        first_response_at TIMESTAMP,
        resolved_at TIMESTAMP,
        FROM TABLE(tutorial_support.tickets)
    ),
    TABLES.EVENTS := EXTERNAL_COLUMNS(
        event_id BIGINT#EVENTS BIND TO EVENT_ID,
        ticket_id BIGINT#TICKETS,
        customer_id BIGINT#CUSTOMERS,
        agent_id BIGINT#AGENTS,
        event_type VARCHAR,
        ts TIMESTAMP,
        FROM TABLE(tutorial_support.events)
    ),
    TABLES.SURVEYS := EXTERNAL_COLUMNS(
        survey_id BIGINT#SURVEYS BIND TO SURVEY_ID,
        ticket_id BIGINT#TICKETS,
        customer_id BIGINT#CUSTOMERS,
        score BIGINT,
        FROM TABLE(tutorial_support.surveys)
    ),
    TABLES.AGENTS := EXTERNAL_COLUMNS(
        agent_id BIGINT#AGENTS BIND TO AGENT_ID,
        name VARCHAR,
        team VARCHAR,
        FROM TABLE(tutorial_support.agents)
    ),
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.SUPPORT.TABLES.CUSTOMERS	CREATED	Feature created as not exists
FM.SUPPORT.TABLES.TICKETS	CREATED	Feature created as not exists
FM.SUPPORT.TABLES.EVENTS	CREATED	Feature created as not exists
FM.SUPPORT.TABLES.SURVEYS	CREATED	Feature created as not exists
FM.SUPPORT.TABLES.AGENTS	CREATED	Feature created as not exists

1. Start at the customer grain

RELATED() replaces a child-table GROUP BY plus join. CUSTOMER_ID is the outer key, while NESTED TICKET_ID := @BIND_KEYSET(TABLES.TICKETS) supplies the ticket keys needed by the child computation.

FeatureQL

SELECT
    CUSTOMER := TABLES.CUSTOMERS[name],
    PLAN := TABLES.CUSTOMERS[plan],
    TICKET_COUNT := CUSTOMER_ID.RELATED(
        COUNT(1) GROUP BY TABLES.TICKETS[customer_id]
    ),
    OPEN_TICKETS := CUSTOMER_ID.RELATED(
        COUNT_IF(TABLES.TICKETS[status] = 'open')
        GROUP BY TABLES.TICKETS[customer_id]
    )
FROM FM.SUPPORT
FOR
    CUSTOMER_ID := @BIND_KEYSET(TABLES.CUSTOMERS),
    NESTED TICKET_ID := @BIND_KEYSET(TABLES.TICKETS)
ORDER BY CUSTOMER;

Result

CUSTOMER VARCHAR	PLAN VARCHAR	TICKET_COUNT BIGINT	OPEN_TICKETS BIGINT
Atlas	pro	2	1
Beacon	free	1	0
Cobalt	enterprise	2	1
Delta	pro	1	0

Atlas and Cobalt each have one open ticket.

2. Change grain to tickets

The same persisted namespace can answer a ticket-level question. Bind TICKET_ID directly, then follow its customer foreign key back to the customer name.

FeatureQL

SELECT
    TICKET_ID,
    TICKET_CUSTOMER_ID := TABLES.TICKETS[customer_id],
    CUSTOMER := RELATED(
        TABLES.CUSTOMERS[name] VIA TABLES.TICKETS[customer_id]
    ),
    PRIORITY := TABLES.TICKETS[priority],
    STATUS := TABLES.TICKETS[status]
FROM FM.SUPPORT
FOR
    TICKET_ID := @BIND_KEYSET(TABLES.TICKETS)
ORDER BY TICKET_ID;

Result

TICKET_ID BIGINT	TICKET_CUSTOMER_ID BIGINT	CUSTOMER VARCHAR	PRIORITY VARCHAR	STATUS VARCHAR
101	1	Atlas	urgent	closed
102	1	Atlas	normal	open
201	2	Beacon	high	closed
301	3	Cobalt	urgent	closed
302	3	Cobalt	normal	open
401	4	Delta	high	closed

The output has one row per ticket, not one row per customer.

3. Compute SLA metrics inside an array

Collect child rows with ARRAY_AGG(ROW(...)), then use TRANSFORM() for per-ticket calculations. UNWRAP_ONE() converts the one-row aggregate result back to a scalar.

FeatureQL

WITH
    CUSTOMER_TICKETS := CUSTOMER_ID.RELATED(
        ARRAY_AGG(ROW(
            TABLES.TICKETS[ticket_id] AS ticket_id,
            TABLES.TICKETS[created_at] AS created_at,
            TABLES.TICKETS[first_response_at] AS first_response_at
        )) GROUP BY TABLES.TICKETS[customer_id]
    ),
    RESPONSE_STATS := CUSTOMER_TICKETS.TRANSFORM(
        SELECT
            DATE_DIFF(first_response_at, created_at, 'minute') AS response_minutes
    ),
    SLA_BREACHES := RESPONSE_STATS.TRANSFORM(
        SELECT COUNT(*) FILTER (
            WHERE response_minutes IS NULL OR response_minutes > 60
        )
    ).UNWRAP_ONE(),
    AVG_RESPONSE_MINUTES := RESPONSE_STATS.TRANSFORM(
        SELECT AVG(response_minutes)
    ).UNWRAP_ONE()::BIGINT,
SELECT
    TABLES.CUSTOMERS[name] AS CUSTOMER,
    COALESCE(SLA_BREACHES, 0::BIGINT),
    AVG_RESPONSE_MINUTES
FROM FM.SUPPORT
FOR
    CUSTOMER_ID := @BIND_KEYSET(TABLES.CUSTOMERS),
    NESTED TICKET_ID := @BIND_KEYSET(TABLES.TICKETS)
ORDER BY CUSTOMER;

Result

CUSTOMER VARCHAR	SLA_BREACHES BIGINT	AVG_RESPONSE_MINUTES BIGINT
Atlas	1	15
Beacon	1	1590
Cobalt	1	5
Delta	0	30

An unanswered ticket counts as an SLA breach here. Atlas has one breach and a 15-minute average among answered tickets; Beacon’s answered ticket took 1590 minutes.

4. Keep grouped detail as an array

TRANSFORM() can group an array without changing the outer customer grain. The result is a typed array of rows, so each customer keeps its own priority distribution.

FeatureQL

WITH
    CUSTOMER_TICKETS := CUSTOMER_ID.RELATED(
        ARRAY_AGG(ROW(TABLES.TICKETS[priority] AS priority_level))
        GROUP BY TABLES.TICKETS[customer_id]
    ),
    PRIORITIES := CUSTOMER_TICKETS.TRANSFORM(
        SELECT priority_level, COUNT(1) GROUP BY priority_level AS ticket_count
        ORDER BY priority_level
    ),
SELECT
    TABLES.CUSTOMERS[name] AS CUSTOMER,
    PRIORITIES
FROM FM.SUPPORT
FOR
    CUSTOMER_ID := @BIND_KEYSET(TABLES.CUSTOMERS),
    NESTED TICKET_ID := @BIND_KEYSET(TABLES.TICKETS)
ORDER BY CUSTOMER;

Result

CUSTOMER VARCHAR	PRIORITIES VARBINARY
Atlas	[{priority_level: normal, ticket_count: 1}, {priority_level: urgent, ticket_count: 1}]
Beacon	[{priority_level: high, ticket_count: 1}]
Cobalt	[{priority_level: normal, ticket_count: 1}, {priority_level: urgent, ticket_count: 1}]
Delta	[{priority_level: high, ticket_count: 1}]

Atlas and Cobalt each have one normal and one urgent ticket.

5. Use `FILTER (WHERE ...)` for conditional aggregates

The positive-response count is conditional, but the denominator is all responses. FILTER (WHERE ...) keeps those two populations separate without filtering away rows before the aggregate runs.

FeatureQL

WITH
    RESPONSES := CUSTOMER_ID.RELATED(
        COUNT(TABLES.SURVEYS[score]) GROUP BY TABLES.SURVEYS[customer_id]
    ),
    POSITIVE := CUSTOMER_ID.RELATED(
        COUNT(*) FILTER (WHERE TABLES.SURVEYS[score] >= 4)
        GROUP BY TABLES.SURVEYS[customer_id]
    ),
    POSITIVE_RATE := ROUND(
        100e0 * POSITIVE::DOUBLE / RESPONSES::DOUBLE,
        1
    ),
SELECT
    TABLES.CUSTOMERS[plan] AS PLAN,
    RESPONSES,
    POSITIVE_RATE
FROM FM.SUPPORT
FOR
    CUSTOMER_ID := @BIND_KEYSET(TABLES.CUSTOMERS),
    NESTED TICKET_ID := @BIND_KEYSET(TABLES.TICKETS),
    NESTED SURVEY_ID := @BIND_KEYSET(TABLES.SURVEYS)
ORDER BY PLAN, POSITIVE_RATE;

Result

PLAN VARCHAR	RESPONSES BIGINT	POSITIVE_RATE DECIMAL
enterprise	1	100
free	1	0
pro	1	0
pro	1	100

The plan-level output is intentionally not pre-aggregated: two pro customers produce two rows with different satisfaction rates.

6. Derive an escalation rate

Features can be built from a different child table while the outer grain stays customer. Count all events and escalated events independently, then divide after RELATED() has aligned both results.

FeatureQL

WITH
    EVENT_COUNT := CUSTOMER_ID.RELATED(
        COUNT(1) GROUP BY TABLES.EVENTS[customer_id]
    ),
    ESCALATIONS := CUSTOMER_ID.RELATED(
        COUNT_IF(TABLES.EVENTS[event_type] = 'escalated')
        GROUP BY TABLES.EVENTS[customer_id]
    ),
    ESCALATION_RATE := ROUND(
        100e0 * COALESCE(ESCALATIONS, 0::BIGINT)::DOUBLE / EVENT_COUNT::DOUBLE,
        1
    ),
SELECT
    TABLES.CUSTOMERS[name] AS CUSTOMER,
    EVENT_COUNT,
    COALESCE(ESCALATIONS, 0::BIGINT),
    ESCALATION_RATE
FROM FM.SUPPORT
FOR
    CUSTOMER_ID := @BIND_KEYSET(TABLES.CUSTOMERS),
    NESTED EVENT_ID := @BIND_KEYSET(TABLES.EVENTS)
ORDER BY CUSTOMER;

Result

CUSTOMER VARCHAR	EVENT_COUNT BIGINT	ESCALATIONS BIGINT	ESCALATION_RATE DECIMAL
Atlas	4	1	25
Beacon	2	0	0
Cobalt	3	1	33.3
Delta	2	0	0

Atlas escalated 1 of 4 events; Cobalt escalated 1 of 3.

7. Filter and reshape selected child rows

Filter the nested ticket array to open rows, then project only the fields needed by the result. The array stays at the customer grain instead of exploding the result into one row per ticket.

FeatureQL

WITH
    CUSTOMER_TICKETS := CUSTOMER_ID.RELATED(
        ARRAY_AGG(ROW(
            TABLES.TICKETS[ticket_id] AS ticket_id,
            TABLES.TICKETS[priority] AS priority,
            TABLES.TICKETS[status] AS status
        )) GROUP BY TABLES.TICKETS[customer_id]
    ),
    OPEN_DETAILS := CUSTOMER_TICKETS.TRANSFORM(
        SELECT ticket_id, priority WHERE status = 'open'
    ),
SELECT
    TABLES.CUSTOMERS[name] AS CUSTOMER,
    OPEN_DETAILS
FROM FM.SUPPORT
FOR
    CUSTOMER_ID := @BIND_KEYSET(TABLES.CUSTOMERS),
    NESTED TICKET_ID := @BIND_KEYSET(TABLES.TICKETS)
ORDER BY CUSTOMER;

Result

CUSTOMER VARCHAR	OPEN_DETAILS VARBINARY
Atlas	[{ticket_id: 102, priority: normal}]
Beacon	[]
Cobalt	[{ticket_id: 302, priority: normal}]
Delta	[]

The empty arrays are meaningful: Beacon and Delta have no open tickets.

8. Move the same event relationship to agents

The relationship is not tied to customers. Bind AGENT_ID instead and aggregate the same event source by its agent foreign key.

FeatureQL

WITH
    EVENT_COUNT := AGENT_ID.RELATED(
        COUNT(1) GROUP BY TABLES.EVENTS[agent_id]
    ),
    ESCALATIONS := AGENT_ID.RELATED(
        COUNT_IF(TABLES.EVENTS[event_type] = 'escalated')
        GROUP BY TABLES.EVENTS[agent_id]
    ),
SELECT
    TABLES.AGENTS[name] AS AGENT,
    TABLES.AGENTS[team] AS TEAM,
    EVENT_COUNT,
    COALESCE(ESCALATIONS, 0::BIGINT)
FROM FM.SUPPORT
FOR
    AGENT_ID := @BIND_KEYSET(TABLES.AGENTS),
    NESTED EVENT_ID := @BIND_KEYSET(TABLES.EVENTS)
ORDER BY AGENT;

Result

AGENT VARCHAR	TEAM VARCHAR	EVENT_COUNT BIGINT	ESCALATIONS BIGINT
Iris	billing	4	0
Jon	technical	3	1
Mina	technical	4	1

Jon handled three events and one escalation; Mina handled four events and one escalation.

What's next

Analytics overview — concept map for this series
Product analytics — persisted event features and funnels
OBT modeling — deeper nested arrays and profile ROWs
RELATED() and EXTEND() — full syntax

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

On this page

Data Model 1. Start at the customer grain 2. Change grain to tickets 3. Compute SLA metrics inside an array 4. Keep grouped detail as an array 5. Use FILTER (WHERE ...) for conditional aggregates 6. Derive an escalation rate 7. Filter and reshape selected child rows 8. Move the same event relationship to agents What's next