Window & Ranking Functions

Window and ranking functions operate over a set of rows defined by a partition and an ordering. They are used to compute rankings, row numbers, and access values from neighboring rows without collapsing the result set into groups.

Window functions in Trilogy use the following syntax pattern:

window_function: FUNCTION_NAME concept ("over" partition_key)? ("by" | "order by") sort_expression

The over clause defines the partition (grouping) for the window, and by/order by defines the sort order within each partition.

RANK

Assigns a rank to each row within a partition based on the specified ordering. Rows with equal values receive the same rank, and the next rank is incremented by the number of tied rows (e.g., 1, 2, 2, 4).

Inputs:

Any (concept to rank)Any (partition key, optional)Any (sort expression)

Output:

INTEGER

Purpose:

property

Example:

rank product.id over category.id by sum(sales.amount) desc as product_rank

Tips

The concept after rank identifies which entity is being ranked. The over clause specifies the partition, and by specifies the ordering.

ROW_NUMBER

Assigns a unique sequential integer to each row within a partition, starting at 1. Unlike RANK, ROW_NUMBER always produces distinct values even for ties.

Inputs:

Any (concept for grain)Any (partition key, optional)Any (sort expression)

Output:

INTEGER

Purpose:

property

Example:

row_number order.id over customer.id by order.date desc as order_sequence

LAG

Accesses a value from a previous row within the partition, based on the specified offset and ordering. Useful for computing differences between consecutive rows, such as period-over-period changes.

Inputs:

INTEGER (offset)Any (concept to access)Any (partition key, optional)Any (sort expression)

Output:

Same as input concept type

Purpose:

property

Example:

lag 1 monthly_revenue over product.id by report_month as previous_month_revenue

Tips

The integer after lag specifies how many rows back to look. lag 1 gets the immediately preceding row's value.

LEAD

Accesses a value from a subsequent row within the partition, based on the specified offset and ordering. Useful for computing forward-looking comparisons.

Inputs:

INTEGER (offset)Any (concept to access)Any (partition key, optional)Any (sort expression)

Output:

Same as input concept type

Purpose:

property

Example:

lead 1 monthly_revenue over product.id by report_month as next_month_revenue

Full Example

import store_sales as store_sales;

# Get the top 3 items per state
select
    store_sales.item.name,
    store_sales.customer.state,
    sum(store_sales.sales_price) as state_item_sales,
    rank store_sales.item.name
        over store_sales.customer.state
        order by sum(store_sales.sales_price)
            by store_sales.item.name, store_sales.customer.state desc
    as sales_rank
having sales_rank <=3
order by
    store_sales.customer.state asc,
    sales_rank asc;