Shape · Rows

Rank

Ranks rows by one or more columns, optionally within groups, so the top seller per region gets 1. You choose how ties are ranked; percentile rank is available too, and Tile splits the rows into N equal-sized buckets.

Also known as: Dense rank, percentile

How it works

Group by restarts the ranking for each group. Order by lists the keys the rank follows, each with a Direction of Asc or Desc and Nulls First or Last. Method decides ties: Average, Min and Max give tied rows the same number, First numbers them by row order, Dense leaves no gaps after a tie, and Tile (equal count) puts the rows into Tiles buckets of equal size, 5 by default, in row order when Order by is empty. Output column defaults to <column>_rank or <column>_tile. Percentile [0, 1] replaces the rank with a percentile for every method except Tile. For Dense, Order ids by chooses Sorted key value or First appearance, which numbers each distinct key in the order it first shows up, and Start at and Step let Dense and Tile ids begin anywhere and count in steps.

Inputs and outputs

Settings

{
  orderBy: [{ column: string, direction: "asc"|"desc", nulls: "first"|"last" }]
  method: "average"|"min"|"max"|"first"|"dense"|"tile"
  groupBy?: string[]
  outputColumn?: string
  pct: boolean
  idOrder?: "value"|"appearance" (dense: ids by sorted key or by first appearance)
  tiles?: number (tile: bucket count, default 5; orderBy may be empty = row order)
  from?: number
  step?: number (dense/tile ids = from + (n - 1) * step, default 1/1)
}

Example

The top seller in each region gets 1: Order by sales Descending, Method Min, Group by region. Two sellers tied for first both get 1 and the next gets 3; with Dense the next would get 2.

More in Rows

• Filter — Keeps only the rows that meet your conditions, combined with AND or OR. For example, keep orders where the amount is over 100.
• Switch — Sends each row down one of several outputs: it goes to the first case whose condition is true, and rows that match none go to Default.
• Sort — Puts rows in order by one or more columns, ascending or descending, with blanks first or last.
• Row Limit — Keeps or drops the first or last N rows, in their current order. For example, skip three header lines or take 100 rows for a quick check.
• Top / Bottom N — Keeps the N rows with the largest or smallest values in one column, such as the 10 best-selling products. You choose what happens to ties at the cut-off.
• Distinct — Keeps one row per unique combination of key columns. You choose whether the first or last copy survives; with no keys it drops exact duplicate rows.
• Random Sample — Picks a random set of rows, either a fixed number or a share such as 10%. You can sample with replacement, or stratify by a column so each group keeps its share. Set a seed to get the same rows every time.
• Random Split — Divides rows into named groups by percentage, each on its own output, such as 80/20 train and test. Repeatable, and can keep each group's share of a column.
• Add Row Number — Numbers the rows 1, 2, 3… in a new column. You can sort first and restart the count in each group.
• Densify — Adds the rows that are missing so every key combination appears, taken from the data or from a reference table you wire in, and fills the gaps with values you choose. For example, every product for every month, with 0 sales where none were recorded.
• Generate Rows — Repeats each row once per value of a range, such as 1 to a count column or a start date to an end date, and adds the generated value as a column. For a sequence with no input, use Generate Series.