SQLCheat Sheet

SQL Window Functions Cheat Sheet

Quick-reference for window functions: ranking, offsets, cumulative aggregates, and frame clauses. Each section includes copy-ready snippets with inline output comments.

Window Function Syntax

Window functions compute a value across a set of rows related to the current row — without collapsing rows like GROUP BY.

Basic structure

-- function() OVER (
--   PARTITION BY ...   -- optional: divide into groups
--   ORDER BY ...       -- optional: define row ordering
--   frame_clause       -- optional: limit which rows to include
-- )
SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

OVER() with no clauses = entire result set

-- Compare each salary to the global average
SELECT name, salary,
  salary - AVG(salary) OVER () AS diff_from_avg
FROM employees;

ROW_NUMBER, RANK, DENSE_RANK

Assign numbers to rows within partitions. They differ in how ties are handled.

Comparison on tied values

-- Given scores: 100, 90, 90, 80
-- ROW_NUMBER: 1, 2, 3, 4  (unique, arbitrary tie-break)
-- RANK:       1, 2, 2, 4  (ties share rank, then gap)
-- DENSE_RANK: 1, 2, 2, 3  (ties share rank, no gap)
SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players;

ROW_NUMBER with tie-breaker

-- Always add a unique column to prevent unstable results
SELECT *,
  ROW_NUMBER() OVER (
    ORDER BY score DESC, id ASC  -- id breaks ties
  ) AS row_num
FROM players;

Top-N per Group (CTE Pattern)

Window functions cannot be filtered in WHERE. Wrap in a CTE, then filter in the outer query.

Top 3 orders per customer

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY amount DESC, id DESC
    ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

Dedup: keep latest row per group

WITH latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC, id DESC
    ) AS rn
  FROM events
)
SELECT * FROM latest WHERE rn = 1;

Cannot write WHERE ROW_NUMBER() <= 3 directly — window functions run after WHERE.

PARTITION BY and ORDER BY

PARTITION BY divides rows into independent groups. ORDER BY within OVER controls which rows are "before" and "after."

PARTITION BY

-- Running count within each department
SELECT name, department,
  COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees;

ORDER BY in OVER (does NOT sort output)

-- ORDER BY inside OVER controls window ordering only
SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM players;
-- Output order is NOT guaranteed!
-- Add a query-level ORDER BY to sort results

LAG and LEAD

Access a previous (LAG) or next (LEAD) row without a self-join. Optional default for partition edges.

Day-over-day change

SELECT
  date,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_metrics;

LEAD: peek at next row

SELECT
  event_name,
  event_date,
  LEAD(event_date) OVER (ORDER BY event_date) AS next_event
FROM events;

Default value at partition edges

-- LAG returns NULL at partition start; provide a default
SELECT
  date,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue
FROM daily_metrics;
-- First row: prev_revenue = 0 (not NULL)

FIRST_VALUE and LAST_VALUE

Return the first or last value in the window frame. LAST_VALUE has a frame trap — the default frame ends at the current row.

FIRST_VALUE (works with default frame)

SELECT customer_id, order_date, amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS first_order_amount
FROM orders;

LAST_VALUE (requires extended frame)

-- Default frame ends at CURRENT ROW — returns current value!
-- Fix: extend to UNBOUNDED FOLLOWING
SELECT customer_id, order_date, amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders;

Without the explicit frame, LAST_VALUE returns the current row — not the partition last.

Running Totals and Cumulative Aggregates

Use aggregate functions with OVER to compute cumulative values. Always specify a ROWS frame for deterministic results.

Running total (cumulative sum)

SELECT date, amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM transactions;

Running count

SELECT date,
  COUNT(*) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_count
FROM events;

Moving average (7-day window)

SELECT date, value,
  AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS avg_7d
FROM daily_metrics;

6 PRECEDING + CURRENT ROW = 7 rows. Early rows average fewer values.

NTILE

Divide rows into N approximately equal-sized buckets.

Quartiles

SELECT name, score,
  NTILE(4) OVER (ORDER BY score) AS quartile
FROM students;
-- quartile 1 = bottom 25%, quartile 4 = top 25%

Percentile rank

SELECT name, score,
  PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
  CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM students;
-- pct_rank: 0.0 to 1.0 (relative position)
-- cume_dist: fraction of rows <= current value

ROWS vs RANGE Frame

ROWS counts physical positions. RANGE groups rows with equal ORDER BY values (ties). The default with ORDER BY is RANGE — which causes surprises with running totals.

Default RANGE groups ties

-- Two rows with same date get the SAME running total
-- | date       | amount | running_total |
-- | 2024-01-01 |    100 |           300 |  <-- both Jan 1
-- | 2024-01-01 |    200 |           300 |  <-- same total!
-- | 2024-01-02 |    150 |           450 |
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — includes all tied rows.

Fix: use ROWS for deterministic running totals

SELECT date, amount,
  SUM(amount) OVER (
    ORDER BY date, id   -- tie-breaker
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM transactions;
-- Each row now has a unique cumulative total

Named Windows (WINDOW Clause)

Define a reusable window specification to avoid repeating OVER(...) in multiple columns.

WINDOW clause (PostgreSQL, MySQL 8+)

SELECT date, amount,
  SUM(amount)   OVER w AS running_sum,
  AVG(amount)   OVER w AS running_avg,
  COUNT(*)      OVER w AS running_count
FROM transactions
WINDOW w AS (
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

Not supported in all databases. SQLite supports it; SQL Server does not.

Percent of Total

Calculate each row's share of the group or overall total using SUM() OVER.

Percent of partition total

SELECT
  category,
  product_name,
  revenue,
  ROUND(
    100.0 * revenue / SUM(revenue) OVER (PARTITION BY category),
    2
  ) AS pct_of_category
FROM product_sales;

Percent of grand total

SELECT
  category,
  COUNT(*) AS cnt,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM products
GROUP BY category;

Learn SQL in Depth

SQL Aggregation Practice →SQL CTEs Practice →SQL Subqueries Practice →

Warm-up1 / 2

Can you write this from memory?

Select total and the overall sum of all totals from the orders table. Use a window function to show the sum alongside each row.

Start Practicing SQL

Free daily exercises with spaced repetition. No credit card required.

← Back to SQL Syntax Practice