# Window Functions

Window functions are SQL functions that you can use with [`input_vars`]({{< ref "profiles/dev-docs/profiles-yaml/var-groups/input-var.md" >}}) and [`entity_vars`]({{< ref "profiles/dev-docs/profiles-yaml/var-groups/entity-var.md" >}}).

SQL window functions perform calculations across rows related to the current row, enabling complex analytics like rankings, running totals, and running averages.

```yaml
window:
    order_by:
        - order_column (desc)
    frame_clause: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    partition_by:
        - partition_column
```

| Field | Description |
| :------------- | :---------------------- |
| `order_by` | Column to order rows by, for example, add `desc` for descending order. |
| `frame_clause` | Specify the set of rows (or window) the calculation is applied to, see the [specific warehouse documentation](#window-function-references) for more details. |
| `partition_by` | Column to partition the rows by. Applies only to `input_vars`. |

## Window function references

- [Snowflake](https://docs.snowflake.com/en/sql-reference/functions-window)
- [Redshift](https://docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.html)
- [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls)
- [Databricks](https://docs.databricks.com/en/sql/language-manual/sql-ref-window-functions.html)

## Example

```yaml
# Order by
- entity_var:
    name: order_number
    select: rank() # DO NOT specify frame clause when a ranking window function is used
    window:
        order_by:
            - order_date

# Partition by and Input var
- input_var:
    name: session_start_time
    from: models/rsTracksUnionPages
    select: min(timestamp)
    window:
        partition_by:
            - context_session_id
            - rudder_id
    description: Describes the start time of session of a specific context_id

# Using frame clause

- entity_var:
    name: first_num_b_order_num_b
    select: first_value(tbl_c.num_b) # Specify frame clause as aggregate window function is used
    from: inputs/tbl_c
    default_value: -1
    where: tbl_c.num_b >= 10
    window:
        order_by:
            - tbl_c.num_b desc
        frame_clause: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
```