Window Functions

Rankings, running totals, row numbering — analytics queries that compute values across related rows without collapsing them.

#What are window functions?

Window functions are like aggregates (SUM, COUNT, AVG) but they don't collapse rows into groups. Instead, they compute a value for each row based on a "window" of related rows. This makes them perfect for rankings, running totals, and numbering.

GROUP BY (aggregates)

·Collapses rows into groups
·One output row per group
·Can't see individual records
·SUM, COUNT, AVG

Window functions

·Keeps all rows intact
·Adds computed columns to each row
·See individual records + analytics
·ROW_NUMBER, RANK, SUM OVER

#Available functions

10 lines


1import {
2  rowNumber,    // sequential numbering within a partition
3  rank,         // ranking with gaps (1, 2, 2, 4)
4  denseRank,    // ranking without gaps (1, 2, 2, 3)
5  sumOver,      // running sum within a partition
6  avgOver,      // running average
7  countOver,    // running count
8  minOver,      // running minimum
9  maxOver,      // running maximum
10} from 'zanith';

#Example: Rank posts by views

Which posts have the most views? Rank gives each row a position:

14 lines


1const ranked = await db.post.query()
2  .select(({ post }) => ({
3    title: post.title,
4    viewCount: post.viewCount,
5    viewRank: rank()
6      .orderBy(post.viewCount.desc()),   // rank by views, highest first
7  }))
8  .execute();
9 
10// [
11//   { title: "Viral Post",     viewCount: 10000, viewRank: 1 },
12//   { title: "Popular Post",   viewCount: 5000,  viewRank: 2 },
13//   { title: "Average Post",   viewCount: 500,   viewRank: 3 },
14// ]

#Example: Running total per author

Track cumulative views for each author, ordered by post date:

15 lines


1const timeline = await db.post.query()
2  .with({ author: true })
3  .select(({ post, author }) => ({
4    title: post.title,
5    authorEmail: author.email,
6    // Number posts per author chronologically
7    postNumber: rowNumber()
8      .partitionBy(post.authorId)         // restart numbering per author
9      .orderBy(post.createdAt.asc()),     // order by date
10    // Running total of views per author
11    runningViews: sumOver(post.viewCount)
12      .partitionBy(post.authorId)
13      .orderBy(post.createdAt.asc()),
14  }))
15  .execute();

#partitionBy vs orderBy

Window functions have two configuration methods:

Method	What it does	SQL equivalent
`.partitionBy(fields)`	Defines groups — the window function restarts for each group	`PARTITION BY field`
`.orderBy(fields)`	Defines order within each group	`ORDER BY field ASC/DESC`

9 lines


1// Without partition: rank ALL posts globally
2rank().orderBy(post.viewCount.desc())
3// → RANK() OVER (ORDER BY "view_count" DESC)
4 
5// With partition: rank posts WITHIN each author
6rank()
7  .partitionBy(post.authorId)
8  .orderBy(post.viewCount.desc())
9// → RANK() OVER (PARTITION BY "author_id" ORDER BY "view_count" DESC)

#Function reference

Function	What it computes	Common use
`rowNumber()`	Sequential 1, 2, 3, 4...	Pagination, numbering
`rank()`	Ranking with gaps (1, 2, 2, 4)	Leaderboards
`denseRank()`	Ranking without gaps (1, 2, 2, 3)	Dense rankings
`sumOver(field)`	Running sum	Cumulative totals
`avgOver(field)`	Running average	Moving averages
`countOver()`	Running count	Progressive counts
`minOver(field)`	Running minimum	Track all-time lows
`maxOver(field)`	Running maximum	Track all-time highs