SELECT - Narrative I/O Knowledge Base

The SELECT statement retrieves data from one or more datasets.

Syntax

SELECT [DISTINCT]
  column1 [AS alias1],
  column2 [AS alias2],
  ...
FROM table_reference [AS alias]
[JOIN table_reference ON condition]
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[QUALIFY condition]
[ORDER BY column1 [ASC|DESC], ...]
[LIMIT n [OFFSET m] ROWS]

NQL requires explicit column lists. Wildcards (SELECT * and COUNT(*)) are not supported. See Explicit Column Selection for details.

Clause order

Clauses must appear in this order:

SELECT — columns to return
FROM — data source(s)
JOIN — additional data sources with join conditions
WHERE — row-level filters applied before aggregation
GROUP BY — grouping for aggregations
HAVING — filters applied after aggregation
QUALIFY — filters based on window function results
ORDER BY — result ordering
LIMIT — result count restriction

Examples

Basic query:

SELECT
  user_id,
  email,
  created_at
FROM company_data."123"
WHERE created_at > '2024-01-01'

Query with aggregation:

SELECT
  region,
  COUNT(1) AS user_count,
  AVG(lifetime_value) AS avg_ltv
FROM company_data."456"
GROUP BY region
HAVING COUNT(1) > 100
ORDER BY avg_ltv DESC

Query with window function and QUALIFY:

SELECT
  user_id,
  email,
  last_seen
FROM company_data."123"
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY last_seen DESC
) = 1

Table references

Dataset references

Reference your datasets using the company_data schema with the dataset ID:

FROM company_data."123"
FROM "company_data"."123"

Dataset IDs must be quoted because they are numeric.

Table aliases

Assign aliases to simplify column references:

FROM company_data."123" AS users
SELECT users.email, users.created_at

Rosetta Stone table references

Rosetta Stone provides normalized access to data through three scope levels: global, company-scoped, and dataset-scoped. Each scope determines which data sources your query accesses.

To access another company’s data through Rosetta Stone, that company must have shared their data with you via an access rule. Your own company’s data (company_data) is always accessible. See Access Rules for details on data sharing.

Global access

Query all normalized data from all datasets shared with you:

SELECT
  narrative.rosetta_stone.unique_id,
  narrative.rosetta_stone.event_timestamp
FROM narrative.rosetta_stone
WHERE narrative.rosetta_stone.event_timestamp >= CURRENT_DATE - INTERVAL '30' DAY

Company-scoped access

Query normalized data from a specific company’s datasets:

-- All datasets from your company
SELECT
  company_data._rosetta_stone.unique_id,
  company_data._rosetta_stone.event_timestamp
FROM company_data._rosetta_stone

-- All datasets from a specific company (by slug)
SELECT
  partner_company._rosetta_stone.unique_id,
  partner_company._rosetta_stone.event_timestamp
FROM partner_company._rosetta_stone

Dataset-scoped access

Query normalized data from a specific dataset:

-- By dataset ID
SELECT
  company_data."123"._rosetta_stone.unique_id,
  company_data."123"._rosetta_stone.event_timestamp
FROM company_data."123"._rosetta_stone

-- By dataset slug
SELECT
  company_data.my_dataset._rosetta_stone.unique_id,
  company_data.my_dataset._rosetta_stone.event_timestamp
FROM company_data.my_dataset._rosetta_stone

-- From another company's dataset
SELECT
  partner_company.shared_dataset._rosetta_stone.unique_id,
  partner_company.shared_dataset._rosetta_stone.event_timestamp
FROM partner_company.shared_dataset._rosetta_stone

Choosing a scope level

Scope	Syntax	Use when
Global	`narrative.rosetta_stone`	You want all available normalized data from all sources
Company	`company_data._rosetta_stone`	You want data from a specific company only
Dataset	`company_data."123"._rosetta_stone`	You need to combine normalized and non-normalized columns from the same dataset

See Scoping Your Queries for guidance on choosing the right scope.

Access rules

Query through pre-configured access rules using the provider’s company slug:

FROM provider_company."access_rule_name"

SELECT
  provider_company."ar_fitness".Total_Output
FROM provider_company."ar_fitness"

Column references

Fully qualified names

Use fully qualified column names to avoid ambiguity, especially in joins:

SELECT
  company_data."123".user_id,
  company_data."123".email,
  company_data."456".order_id
FROM company_data."123"
JOIN company_data."456" ON company_data."123".user_id = company_data."456".customer_id

Column aliases

Assign aliases to rename columns in results:

SELECT
  user_id AS id,
  email AS contact_email,
  UPPER(name) AS display_name
FROM company_data."123"

Accessing nested fields

Access struct fields using dot notation:

SELECT
  company_data."1".nested_field.deep_field.value
FROM company_data."1"

Access array elements using bracket notation:

SELECT
  identifiers[0] AS first_id,
  identifiers[0].type AS first_id_type
FROM company_data."1"

Bracket notation also works for struct fields: data['field_name']. This avoids needing to quote reserved keywords. See Reserved Keywords best practices.

Special columns

Price column

Every dataset includes _price_cpm_usd (price per 1,000 rows). Use it to filter by cost:

WHERE company_data."123"._price_cpm_usd <= 1.00

Rosetta Stone field access

Access normalized identity data through the ._rosetta_stone special field on any dataset:

SELECT
  company_data."123"._rosetta_stone.unique_id,
  company_data."123".original_column
FROM company_data."123"

This allows you to access both normalized Rosetta Stone attributes and non-normalized columns from the same dataset row.

Access specific identifier types

Query specific identifier types using the unique_id struct:

SELECT
  narrative.rosetta_stone.unique_id.b."type",
  narrative.rosetta_stone.unique_id.b."value"
FROM narrative.rosetta_stone
WHERE narrative.rosetta_stone.unique_id.b."type" NOT IN ('tdid', 'cookie')

For complete details on scope levels and table references, see Rosetta Stone table references.

DELTA tables

The DELTA function returns only records that have changed since the last query execution. Use it for incremental data processing.

Syntax

FROM DELTA(TABLE table_reference)
FROM DELTA((subquery))

Examples

Delta on a dataset:

SELECT column1
FROM DELTA(TABLE company_data."1") AS t

Delta on Rosetta Stone:

SELECT long_value
FROM DELTA(TABLE narrative.rosetta_stone)

Delta with filtering:

SELECT long_value
FROM DELTA((
  SELECT long_value
  FROM company_data.rosetta_stone
  WHERE long_value > 42
))

Subqueries

Subquery in WHERE

SELECT column1
FROM company_data."1"
WHERE value > (
  SELECT AVG(value)
  FROM company_data."1"
)

Subquery in FROM

SELECT sub.total
FROM (
  SELECT SUM(amount) AS total
  FROM company_data."1"
  GROUP BY category
) AS sub
WHERE sub.total > 1000

EXISTS and NOT EXISTS

SELECT t1.column1
FROM company_data."1" t1
WHERE NOT EXISTS (
  SELECT 1
  FROM company_data."2" t2
  WHERE t1.id = t2.id
)

Common Table Expressions (WITH)

CTEs let you define named subqueries for reuse within a statement.

Syntax

WITH cte_name AS (
  SELECT ...
),
another_cte AS (
  SELECT ...
)
SELECT ...
FROM cte_name

Example

CREATE MATERIALIZED VIEW "high_value_users"
AS (
  WITH daily_totals AS (
    SELECT
      user_id,
      DATE_TRUNC('day', event_timestamp) AS event_date,
      SUM(amount) AS daily_spend
    FROM company_data."1"
    GROUP BY user_id, DATE_TRUNC('day', event_timestamp)
  ),
  user_averages AS (
    SELECT
      user_id,
      AVG(daily_spend) AS avg_daily_spend
    FROM daily_totals
    GROUP BY user_id
  )
  SELECT
    user_id,
    avg_daily_spend
  FROM user_averages
  WHERE avg_daily_spend > 100
)
BUDGET 25 USD

QUALIFY clause

The QUALIFY clause filters results based on window function values. It’s evaluated after window functions, making it useful for deduplication and ranking.

Syntax

SELECT columns
FROM table
QUALIFY window_function_condition

Deduplication example

Keep only the most recent record per user:

SELECT
  user_id,
  email,
  last_seen
FROM company_data."123"
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY last_seen DESC
) = 1

Multiple window conditions

SELECT column1
FROM company_data."1"
QUALIFY
  ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col) = 1
  AND SUM(1) OVER (PARTITION BY other_col) < 10

Budget clauses

Budget clauses control spending on data queries. They are required for CREATE MATERIALIZED VIEW statements.

BUDGET

Set a maximum total spend:

BUDGET 50 USD

LIMIT with USD

Alternative syntax for budget limits:

LIMIT 100 USD

Recurring limits

Set budget limits per time period:

LIMIT 500 USD PER CALENDAR_MONTH
LIMIT 50 USD PER CALENDAR_DAY

Row limits

Limit by row count instead of cost:

LIMIT 1000 ROWS
LIMIT 100 OFFSET 10 ROWS

SELECT DISTINCT

Remove duplicate rows from results:

SELECT DISTINCT category
FROM company_data."123"

CASE expressions

Conditional logic within queries:

SELECT
  user_id,
  CASE
    WHEN lifetime_value > 1000 THEN 'high'
    WHEN lifetime_value > 100 THEN 'medium'
    ELSE 'low'
  END AS value_tier
FROM company_data."123"

Data Types

Primitive and complex types supported in NQL

Operators

Comparison, logical, and arithmetic operators

Functions

Built-in and Narrative-specific functions

CREATE MATERIALIZED VIEW

Complete CREATE MATERIALIZED VIEW reference

Overview

Data Types

SQL Commands

Operators

Functions

General Reference

​Syntax

​Clause order

​Examples

​Table references

​Dataset references

​Table aliases

​Rosetta Stone table references

​Global access

​Company-scoped access

​Dataset-scoped access

​Choosing a scope level

​Access rules

​Column references

​Fully qualified names

​Column aliases

​Accessing nested fields

​Special columns

​Price column

​Rosetta Stone field access

​Access specific identifier types

​DELTA tables

​Syntax

​Examples

​Subqueries

​Subquery in WHERE

​Subquery in FROM

​EXISTS and NOT EXISTS

​Common Table Expressions (WITH)

​Syntax

​Example

​QUALIFY clause

​Syntax

​Deduplication example

​Multiple window conditions

​Budget clauses

​BUDGET

​LIMIT with USD

​Recurring limits

​Row limits

​SELECT DISTINCT

​CASE expressions

​Related content

Data Types

Operators

Functions

CREATE MATERIALIZED VIEW

Syntax

Clause order

Examples

Table references

Dataset references

Table aliases

Rosetta Stone table references

Global access

Company-scoped access

Dataset-scoped access

Choosing a scope level

Access rules

Column references

Fully qualified names

Column aliases

Accessing nested fields

Special columns

Price column

Rosetta Stone field access

Access specific identifier types

DELTA tables

Syntax

Examples

Subqueries

Subquery in WHERE

Subquery in FROM

EXISTS and NOT EXISTS

Common Table Expressions (WITH)

Syntax

Example

QUALIFY clause

Syntax

Deduplication example

Multiple window conditions

Budget clauses

BUDGET

LIMIT with USD

Recurring limits

Row limits

SELECT DISTINCT

CASE expressions

Related content