{
    "api": "YAML JSON TOON Database",
    "version": "1.0.0",
    "format": "json",
    "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\u2026"
            },
            {
                "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": 12,
        "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."
        }
    ]
}