CodeBlanks Logo Icon CodeBlanks
Login Register

What is a window function?

Compute per row with OVER.

What is a window function?

A window function computes over a set of rows (a window). It returns results without collapsing rows.

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk
FROM products;

This query gives each product a price rank.

name, price, RANK() OVER (ORDER BY price DESC) AS rk is selected from products. RANK() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.

What is a window function? — details

A window function does not collapse like GROUP BY. Each row gets its own window result.

This lets you see each product's own price and the average price of all products on the same row, which GROUP BY cannot do.

Practice What is a window function?

Find each product's price rank with RANK() OVER (ORDER BY price DESC).

Starter code

query.sql
-- window function
SELECT name, price,
  RANK()

Sample solution

query.sql
SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk
FROM products;

Expected output

name    price  rk
Laptop  1500   1
Telefon 800    2

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 What does a window function do to rows?

  • Does not collapse them Correct answer
  • Collapses them
  • Deletes them
  • Sorts them

Explanation: A window function does not collapse rows.

2 Add OVER

Question 
RANK() ____ (ORDER BY price DESC)
Answer 
RANK() OVER (ORDER BY price DESC)

Explanation: Window functions are written with OVER; the ORDER BY inside OVER sets the ranking order.

3 What is a window function: match the terms

Term Meaning
window window
OVER over
RANK rank
no collapse preserves

All lessons in Advanced

1 CTE

  1. What is a CTE?
  2. Using WITH
  3. Multiple CTEs
  4. CTE vs subquery
  5. CTE with JOIN
  6. Summary - CTE

2 Recursive CTE

  1. What is a recursive CTE?
  2. Anchor and recursive parts
  3. Walk a hierarchy
  4. Tracking depth
  5. Stopping recursion
  6. Summary - Recursive CTE

3 Window functions

  1. What is a window function?
  2. ROW_NUMBER
  3. RANK and DENSE_RANK
  4. LAG and LEAD
  5. PARTITION BY
  6. Summary - Window functions

4 Project: Analytics report

  1. Analytics design
  2. CTE summary
  3. Top users with ROW_NUMBER
  4. Growth with LAG
  5. Final report

5 Pivot and unpivot

  1. What is pivot?
  2. Pivot with CASE
  3. FILTER clause
  4. Unpivot
  5. Dynamic pivot
  6. Summary - Pivot

6 Materialized view

  1. What is a materialized view?
  2. CREATE
  3. REFRESH
  4. View vs materialized view
  5. Index on materialized views
  6. Summary - Materialized view

7 Stored procedure

  1. What is a procedure?
  2. CREATE PROCEDURE
  3. Parameters
  4. CALL
  5. Control flow
  6. Summary - Stored procedure

8 Function

  1. What is a function?
  2. CREATE FUNCTION
  3. RETURN
  4. Calling a function
  5. Function vs procedure
  6. Summary - Function

9 Trigger

  1. What is a trigger?
  2. BEFORE and AFTER
  3. INSERT trigger
  4. UPDATE trigger
  5. Write an audit trigger
  6. Summary - Trigger

10 Transaction isolation

  1. What is isolation?
  2. READ UNCOMMITTED
  3. READ COMMITTED
  4. REPEATABLE READ
  5. SERIALIZABLE
  6. Summary - Isolation

11 Locking

  1. What is a lock?
  2. Shared and exclusive locks
  3. Deadlock
  4. SELECT FOR UPDATE
  5. Lock timeout
  6. Summary - Locking

12 Query optimization

  1. What is EXPLAIN?
  2. Seq scan vs index scan
  3. Cost
  4. Sargable
  5. N+1 problem
  6. Summary - Optimization

13 Partitioning

  1. What is partitioning?
  2. Range partition
  3. List partition
  4. Hash partition
  5. Partition pruning
  6. Summary - Partitioning

14 Advanced final

  1. Hierarchy report
  2. Window analysis
  3. Procedure and trigger
  4. Optimization