Phase 4: Databases & SQL for AI/ML · ~35 minutes · SQL · SQLite

Window Functions

GROUP BY answers "what's true about this group." A window function answers "what's true about this row, given its group" — and keeps every row, instead of collapsing them into one.

Hiring signal: Reaches for a window function instead of a self-join or application-side loop for per-row-within-group questions

What you will learn

Explain what a window function does differently from GROUP BY: a frame of related rows, not a collapse to one row per group
Use RANK() to rank rows within groups
Use a window SUM() to compute a running total
Decide between GROUP BY and a window function for a given question

Introduction

Type: Learn Languages: SQL Prerequisites: Lesson 03 (Joins and Building Training Datasets) Time: ~35 minutes

Objective

Learning objectives

Explain what a window function does differently from GROUP BY: a frame of related rows, not a collapse to one row per group
Use RANK() to rank rows within groups
Use a window SUM() to compute a running total
Decide between GROUP BY and a window function for a given question

What you're building

Using this lesson's orders table (or your own, from earlier labs in this phase):

Rank each order within its customer's orders by amount, using RANK(), and identify at least one real tie in your data (or construct one) to confirm the skip-on-tie behavior
Compute a running total per customer ordered by date, using a window SUM()
Write one query using SUM() OVER (PARTITION BY ...) without an ORDER BY inside the OVER() clause, and confirm it shows the same full-partition total repeated on every row, not a running total
Write window_vs_groupby.txt explaining, for one question of your own invention, why it specifically needs a window function and couldn't be answered with GROUP BY alone

You need to answer: "What's the average order amount across all customers?" Does this need a window function, or is GROUP BY (or no grouping at all) sufficient?

This question asks for exactly one number — the overall average across everything, with no 'per group' or 'per row, in context of its group' framing at all. AVG(amount) with no GROUP BY computes exactly that single value across the whole table. Neither GROUP BY (which would incorrectly produce one average PER customer instead of one overall) nor a window function (which exists specifically to preserve per-row detail this question doesn't need) is the right tool here — recognizing when NEITHER extra tool is needed is as much a part of choosing correctly as knowing when one of them is.

Unlock the full lesson

You've read the first 2 sections. The rest of this lesson covers The Problem, Check Yourself, Key Terms & Next — plus a hands-on lab, quiz, and project artifact.

Create a free account to unlock Phase 0 and Phase 1 of every course — no credit card.