Rowset Derivation Statements

Rowset derivation statements create named subqueries (CTEs) that can be referenced in subsequent queries within the same script.

Syntax

Two equivalent forms:

rowset_derivation_statement:
    ("rowset" IDENTIFIER "<-" select_statement)
    | ("with" IDENTIFIER "as" select_statement)

Rowset Form

rowset high_value_customers <-
select
    customer.id,
    customer.name,
    sum(order.total) -> lifetime_value
having
    lifetime_value > 10000
;

With Form

CTE-style syntax familiar from SQL:

with high_value_customers as
select
    customer.id,
    customer.name,
    sum(order.total) -> lifetime_value
having
    lifetime_value > 10000
;

Using Rowsets

After definition, reference the rowset in subsequent queries:

rowset active_users <-
select
    user.id,
    user.last_login
where
    user.last_login > current_date() - 30
;
select
    active_users.id,
    count(order.id) -> recent_orders
;

Use Cases

• Breaking complex queries into steps
• Reusing intermediate results
• Improving query readability
• Performance optimization (materialized subqueries)

Example

import customer as customer;
import order as order;
# Define a rowset for recent orders
rowset recent_orders <-
select
    order.customer_id,
    order.id,
    order.date,
    order.total
where
    order.date > current_date() - 90
;
# Use the rowset in analysis
select
    customer.name,
    count(recent_orders.id) -> order_count,
    sum(recent_orders.total) -> recent_spend
order by
    recent_spend desc
;