🧾 SQL Window Functions Cheat Sheet – The Complete Quick Reference

This SQL window functions cheat sheet is the single page you can keep open while you write analytic SQL. Every function, every frame clause, every common pattern — with copy-ready syntax and a link to a practice problem you can solve right now.

Whether you’re prepping for a SQL interview, debugging a dashboard query, or just trying to remember the difference between RANK() and DENSE_RANK() at 11pm, this guide has you covered.

🚀 Why a Window Functions Cheat Sheet

"Xorthax's quartermaster doesn't memorize every starship manual — they keep a reference card on the bridge. You should too."

Window functions are powerful but unforgiving. One missing ORDER BY, one wrong frame clause, and your “running total” returns the same number on every row. This cheat sheet gives you the exact patterns that work, organized so you can find them fast.

• Scan it when you remember the pattern but forgot the syntax.
• Print it for interview prep — every function comes with a "when to use it" cue.
• Click through to the matching practice problem to lock in the muscle memory.

🧬 Window Function Anatomy

Every window function follows the same shape. Memorize this skeleton and you can always fill in the blanks:

FUNCTION(expr) OVER (
  [PARTITION BY partition_col, ...]   -- group rows into windows
  [ORDER BY sort_col [ASC|DESC]]      -- order within each window
  [ROWS|RANGE|GROUPS BETWEEN          -- which rows count for each calc
       frame_start AND frame_end]
)

👉 Want the deep dive on each clause? See How Window Functions Work.

🧮 The Four Function Categories

Every window function belongs to one of four families. Knowing the category tells you what defaults to expect.

➕ Aggregate Window Functions

The same aggregates you already know (SUM, AVG, etc.) — but applied per-row, without collapsing the result set.

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM Orders;

👉 Full guide: Aggregate Window Functions.

🥇 Ranking Functions

Assign a position to each row inside its partition. The differences only matter when there are ties.

SELECT
  CustomerID,
  Region,
  OrderAmount,
  ROW_NUMBER() OVER (PARTITION BY Region ORDER BY OrderAmount DESC) AS rn,
  RANK()       OVER (PARTITION BY Region ORDER BY OrderAmount DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY Region ORDER BY OrderAmount DESC) AS drnk,
  NTILE(4)     OVER (PARTITION BY Region ORDER BY OrderAmount DESC) AS quartile
FROM Orders;

👉 Full guide: Ranking Functions.

🔁 Value & Navigation Functions

Reach into other rows of the partition without writing a self-join.

SELECT
  product_id,
  month,
  revenue,
  LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS mom_change
FROM monthly_revenue;

⚠️ LAST_VALUE gotcha: with the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) it returns the current row, not the partition’s last row. Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

👉 Full guides: LEAD / LAG · FIRST / LAST / NTH.

📊 Distribution Functions

Express where each row sits relative to its peers, on a 0–1 scale.

WITH Ranked AS (
  SELECT *,
         PERCENT_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS pr
  FROM Orders
)
SELECT * FROM Ranked WHERE pr <= 0.10;

👉 Full guide: Percentile & Distribution.

📐 Frame Clauses: ROWS vs RANGE vs GROUPS

The frame clause decides which rows are visible to your function for each row's calculation. Most bugs in window queries trace back to this.

Frame Boundaries Quick Reference

The Defaults That Bite

🧰 Common Patterns (One-Liners You'll Reuse Forever)

The handful of patterns that cover ~80% of real analytics SQL.

🧭 Quick Decision Tree: Which Function Do I Need?

Match the question you’re trying to answer to the function family.

⚠️ Common Pitfalls (Quick Fixes)

• ❌ Forgetting ORDER BY for ranking/value functions — results are non-deterministic. Fix: always include ORDER BY, with a tie-breaker if values can repeat.
• ❌ LAST_VALUE() returning the current row — the default frame stops at CURRENT ROW. Fix: add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• ❌ Using a window function in WHERE — window functions run after WHERE. Fix: wrap the query in a CTE or subquery and filter outside.
• ❌ Confusing RANK with DENSE_RANK — RANK skips numbers after ties; DENSE_RANK doesn’t. Fix: pick based on whether downstream consumers expect contiguous ranks.
• ❌ ROW_NUMBER() changing across runs — the engine breaks ties however it wants. Fix: add a stable secondary column to ORDER BY (e.g. an id).
• ❌ Using ROWS when you needed RANGE (or vice versa) — different semantics with duplicate ORDER BY values. Fix: ROWS = exact row count, RANGE = value-based.
• ❌ Heavy PARTITION BY on huge tables — sort spills kill performance. Fix: filter aggressively first; consider LATERAL joins for top-N at scale.

🌐 Database Differences (At a Glance)

Window function support is broadly consistent — but the syntactic sugar differs. The Reference section has dedicated pages for each function in each database.

👉 For exact syntax in your dialect, browse the Reference section — every function has a dedicated page per database (Postgres, MySQL, SQL Server, MariaDB, SQLite, Snowflake, BigQuery, and more).

🔗 Related Learning Pages

This cheat sheet is a quick reference. The deep dives live here:

• Beginner Series — start here if window functions are brand new.
• How Window Functions Work — the OVER, PARTITION BY, ORDER BY mental model.
• Aggregate Window Functions — running totals, moving averages, percent-of-total.
• Ranking Functions — ROW_NUMBER vs RANK vs DENSE_RANK in depth.
• LEAD / LAG — comparing across rows.
• FIRST / LAST / NTH — boundary values inside a window.
• Percentile & Distribution — PERCENT_RANK, CUME_DIST, NTILE.
• Gap and Island — streaks, gaps, and consecutive-date patterns.

More Resources by Function and Dialect