api: YAML JSON TOON Database
version: 1.0.0
format: toon
dataset:
  id: 1674
  slug: sql-window-functions
  title: SQL Window Functions with Examples
  description: "PostgreSQL window functions and explicit frame patterns for ranks, running totals, and neighboring rows."
  summary: "PostgreSQL window functions and explicit frame patterns for ranks, running totals, and neighboring rows. Each row includes a practical application and a failure to check before using it."
  use_when[2]: Calculate rankings without collapsing detail rows.,Build a running total or compare adjacent events.
  caveats[2]: Examples use PostgreSQL syntax.,An ORDER BY inside OVER does not guarantee final output ordering.
  related[2]: sql-query-types,sql-index-patterns
  reviewed_at: 2026-10-12
  sources[2]{title,url}:
    PostgreSQL window function tutorial,"https://www.postgresql.org/docs/current/tutorial-window.html"
    PostgreSQL window functions,"https://www.postgresql.org/docs/current/functions-window.html"
  reading[1]: sql-window-functions-running-totals-and-ranks
  provenance: "Original editorial reference notes by YJTOON, informed by the linked primary documentation. External specifications retain their own licenses."
  entry_count: 12
  fields[4]{section,field,type,example}:
    data,function,string,ROW_NUMBER
    data,example,string,ROW_NUMBER() OVER (PARTITION BY team ORDER BY s…
    data,use,string,Number rows within each group.
    data,pitfall,string,Add a stable tie-breaker when order matters.
  category: Database Types
  category_slug: database-types
  tags: "database-types,sql window functions,reference,engineering"
  view_count: 3
  created_at: 1791798640
  updated_at: 1791798640
data[12]{function,example,use,pitfall}:
  ROW_NUMBER,"ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id)",Number rows within each group.,Add a stable tie-breaker when order matters.
  RANK,RANK() OVER (ORDER BY score DESC),Give tied rows the same rank with gaps.,It differs from dense rank after a tie.
  DENSE_RANK,DENSE_RANK() OVER (ORDER BY score DESC),Rank distinct score groups without gaps.,It does not choose one winner from tied rows.
  LAG,LAG(amount) OVER (ORDER BY day),Read the preceding row.,The previous row is not necessarily the previous calendar day.
  LEAD,LEAD(amount) OVER (ORDER BY day),Read the following row.,Missing neighbors yield the specified default or null.
  FIRST_VALUE,FIRST_VALUE(price) OVER (PARTITION BY item ORDER BY day),Read the first value in the frame.,Frame semantics affect the result.
  LAST_VALUE,LAST_VALUE(price) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),Read the last value over the whole partition.,The default frame often ends at the current peer group.
  SUM running total,"SUM(amount) OVER (ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)",Compute a deterministic row-by-row cumulative sum.,The default RANGE frame groups peer rows.
  AVG moving rows,AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),Average the current and previous six rows.,Seven rows do not imply seven calendar days.
  COUNT group size,COUNT(*) OVER (PARTITION BY team),Keep detail rows while attaching a group total.,Joining first can duplicate rows and inflate the count.
  NTILE,NTILE(4) OVER (ORDER BY score),Split ordered rows into roughly equal buckets.,Tied values can land in different buckets.
  PERCENT_RANK,PERCENT_RANK() OVER (ORDER BY score),Express relative rank within a partition.,A one-row partition returns zero.