api: YAML JSON TOON Database
version: 1.0.0
format: yaml
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:
    - Calculate rankings without collapsing detail rows.
    - Build a running total or compare adjacent events.
  caveats:
    - Examples use PostgreSQL syntax.
    - An ORDER BY inside OVER does not guarantee final output ordering.
  related:
    - sql-query-types
    - sql-index-patterns
  reviewed_at: "2026-10-12"
  sources:
    - title: PostgreSQL window function tutorial
      url: "https://www.postgresql.org/docs/current/tutorial-window.html"
    - title: PostgreSQL window functions
      url: "https://www.postgresql.org/docs/current/functions-window.html"
  reading:
    - 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:
    - section: data
      field: function
      type: string
      example: ROW_NUMBER
    - section: data
      field: example
      type: string
      example: ROW_NUMBER() OVER (PARTITION BY team ORDER BY s…
    - section: data
      field: use
      type: string
      example: Number rows within each group.
    - section: data
      field: pitfall
      type: string
      example: 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: 13
  created_at: 1791798640
  updated_at: 1791798640
data:
  - function: ROW_NUMBER
    example: ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id)
    use: Number rows within each group.
    pitfall: Add a stable tie-breaker when order matters.
  - function: RANK
    example: RANK() OVER (ORDER BY score DESC)
    use: Give tied rows the same rank with gaps.
    pitfall: It differs from dense rank after a tie.
  - function: DENSE_RANK
    example: DENSE_RANK() OVER (ORDER BY score DESC)
    use: Rank distinct score groups without gaps.
    pitfall: It does not choose one winner from tied rows.
  - function: LAG
    example: LAG(amount) OVER (ORDER BY day)
    use: Read the preceding row.
    pitfall: The previous row is not necessarily the previous calendar day.
  - function: LEAD
    example: LEAD(amount) OVER (ORDER BY day)
    use: Read the following row.
    pitfall: Missing neighbors yield the specified default or null.
  - function: FIRST_VALUE
    example: FIRST_VALUE(price) OVER (PARTITION BY item ORDER BY day)
    use: Read the first value in the frame.
    pitfall: Frame semantics affect the result.
  - function: LAST_VALUE
    example: LAST_VALUE(price) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    use: Read the last value over the whole partition.
    pitfall: The default frame often ends at the current peer group.
  - function: SUM running total
    example: SUM(amount) OVER (ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    use: Compute a deterministic row-by-row cumulative sum.
    pitfall: The default RANGE frame groups peer rows.
  - function: AVG moving rows
    example: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
    use: Average the current and previous six rows.
    pitfall: Seven rows do not imply seven calendar days.
  - function: COUNT group size
    example: COUNT(*) OVER (PARTITION BY team)
    use: Keep detail rows while attaching a group total.
    pitfall: Joining first can duplicate rows and inflate the count.
  - function: NTILE
    example: NTILE(4) OVER (ORDER BY score)
    use: Split ordered rows into roughly equal buckets.
    pitfall: Tied values can land in different buckets.
  - function: PERCENT_RANK
    example: PERCENT_RANK() OVER (ORDER BY score)
    use: Express relative rank within a partition.
    pitfall: A one-row partition returns zero.