Last Updated: 3/20/2026

Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing the result set like aggregate functions with GROUP BY do. Kysely supports window functions through the .over() method on aggregate function builders.

Basic Window Functions

Call .over() on any aggregate function to create a window function. The simplest form uses an empty window:


const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    (eb) => eb.fn.avg<number>('age').over().as('average_age')
  ])
  .execute()

The generated SQL (PostgreSQL):


select "first_name", avg("age") over() as "average_age"
from "person"

This calculates the average age across all rows. Every row in the result includes the same average_age value, but unlike a GROUP BY query, each row retains its individual first_name.

Partitioning Windows

Use .partitionBy() inside the .over() callback to divide rows into groups. The window function calculates separately for each partition:


const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    'last_name',
    (eb) => eb.fn.avg<number>('age').over(
      ob => ob.partitionBy('last_name')
    ).as('avg_age_by_last_name')
  ])
  .execute()

The generated SQL (PostgreSQL):


select "first_name", "last_name", avg("age") over(partition by "last_name") as "avg_age_by_last_name"
from "person"

Each person sees the average age of all people who share their last name. The partitionBy method (defined in src/query-builder/over-builder.ts) accepts a single column reference or an array of column references:


(eb) => eb.fn.count<number>('id').over(
  ob => ob.partitionBy(['last_name', 'gender'])
).as('count_by_name_and_gender')

Ordering Within Windows

Use .orderBy() inside the .over() callback to define the order in which rows are processed within each partition. This is essential for ranking functions and running totals:


const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    'age',
    (eb) => eb.fn.avg<number>('age').over(
      ob => ob.orderBy('age', 'desc')
    ).as('running_avg')
  ])
  .execute()

The generated SQL (PostgreSQL):


select "first_name", "age", avg("age") over(order by "age" desc) as "running_avg"
from "person"

The orderBy method in OverBuilder (see src/query-builder/over-builder.ts) works like the top-level orderBy method on query builders. You can specify direction ('asc' or 'desc') and chain multiple calls:


(eb) => eb.fn.sum<number>('salary').over(
  ob => ob
    .orderBy('department', 'asc')
    .orderBy('hire_date', 'desc')
).as('cumulative_salary')

Combining Partition and Order

Most window functions use both partitioning and ordering. Partition defines the groups, and order defines the sequence within each group:


const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    'last_name',
    'age',
    (eb) => eb.fn.avg<number>('age').over(
      ob => ob
        .partitionBy('last_name')
        .orderBy('age', 'asc')
    ).as('running_avg_age')
  ])
  .execute()

The generated SQL (PostgreSQL):


select "first_name", "last_name", "age",
  avg("age") over(partition by "last_name" order by "age" asc) as "running_avg_age"
from "person"

This calculates a running average of age within each last name group, ordered by age.

Ranking Functions

Kysely provides access to ranking window functions through eb.fn.agg(). Common ranking functions include row_number(), rank(), dense_rank(), and ntile():


const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    'age',
    (eb) => eb.fn.agg<number>('row_number').over(
      ob => ob.orderBy('age', 'desc')
    ).as('age_rank')
  ])
  .execute()

The generated SQL (PostgreSQL):


select "first_name", "age",
  row_number() over(order by "age" desc) as "age_rank"
from "person"

The row_number() function assigns a unique sequential number to each row within the window. Use rank() when you want ties to receive the same rank with gaps, or dense_rank() for ties without gaps:


const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    'age',
    (eb) => eb.fn.agg<number>('rank').over(
      ob => ob.orderBy('age', 'desc')
    ).as('rank'),
    (eb) => eb.fn.agg<number>('dense_rank').over(
      ob => ob.orderBy('age', 'desc')
    ).as('dense_rank')
  ])
  .execute()

Offset Functions

Offset functions like lag() and lead() access rows at a specified offset from the current row. These are useful for comparing values between consecutive rows:


const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    'age',
    (eb) => eb.fn.agg<number | null>('lag', ['age']).over(
      ob => ob.orderBy('age', 'asc')
    ).as('previous_age'),
    (eb) => eb.fn.agg<number | null>('lead', ['age']).over(
      ob => ob.orderBy('age', 'asc')
    ).as('next_age')
  ])
  .execute()

The generated SQL (PostgreSQL):


select "first_name", "age",
  lag("age") over(order by "age" asc) as "previous_age",
  lead("age") over(order by "age" asc) as "next_age"
from "person"

The lag() function returns the value from the previous row (or null for the first row), and lead() returns the value from the next row (or null for the last row). You can specify an offset as the second argument:


(eb) => eb.fn.agg<number | null>('lag', ['age', eb.lit(2)]).over(
  ob => ob.orderBy('age', 'asc')
).as('age_two_rows_back')

Aggregate Functions as Window Functions

Any aggregate function can become a window function by calling .over(). The AggregateFunctionBuilder class (defined in src/query-builder/aggregate-function-builder.ts) provides the .over() method that returns a new builder with an OverNode attached.

Common aggregate functions used as window functions:


const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    'age',
    (eb) => eb.fn.count<number>('id').over(
      ob => ob.partitionBy('last_name')
    ).as('people_with_same_last_name'),
    (eb) => eb.fn.sum<number>('age').over(
      ob => ob.orderBy('age', 'asc')
    ).as('cumulative_age'),
    (eb) => eb.fn.max<number>('age').over().as('max_age_overall'),
    (eb) => eb.fn.min<number>('age').over(
      ob => ob.partitionBy('last_name')
    ).as('min_age_in_family')
  ])
  .execute()

The OverBuilder Interface

The OverBuilder class (defined in src/query-builder/over-builder.ts) provides methods for configuring window specifications:

partitionBy(column): Divide rows into partitions
partitionBy([column1, column2]): Partition by multiple columns
orderBy(column, direction): Order rows within the window
clearOrderBy(): Remove all order by clauses

The builder is immutable—each method returns a new instance. This allows you to compose window specifications:


const baseWindow = (ob: OverBuilder<DB, 'person'>) => 
  ob.partitionBy('last_name')
 
const result = await db
  .selectFrom('person')
  .select([
    'first_name',
    (eb) => eb.fn.avg<number>('age').over(
      ob => baseWindow(ob).orderBy('age', 'asc')
    ).as('running_avg')
  ])
  .execute()

What’s Next

Expressions: Learn about the expression builder API, including aggregate functions like count(), sum(), avg(), min(), and max().
CTEs (Common Table Expressions): Combine window functions with CTEs for complex analytical queries.