OVER, PARTITION BY, ORDER BY
Understand the window function model: OVER, PARTITION BY (group), ORDER BY (sort), and how it differs from GROUP BY.
OVER, PARTITION BY, ORDER BY is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is a Window Function?
A window function computes a value across a set of rows related to the current row — without collapsing the result like GROUP BY does. You keep every row AND get an aggregate column.
Basic Example
Average order total across the whole table, on every row:
SELECT id, total,
AVG(total) OVER () AS avg_total
FROM orders;OVER Clause
The OVER (...) clause defines the window. Empty parentheses mean "the whole result set".
SELECT id, total,
SUM(total) OVER () AS grand_total
FROM orders;PARTITION BY: Group the Window
Like GROUP BY, but the rows aren't collapsed:
SELECT id, user_id, total,
SUM(total) OVER (PARTITION BY user_id) AS user_total
FROM orders;
-- Every order keeps its row; user_total is the same for all of one user's orders.ORDER BY in the Window
ORDER BY inside OVER defines the row ordering for running calculations:
SELECT id, user_id, total,
SUM(total) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM orders;
-- Running total per user, ordered by date.Window vs GROUP BY
Side-by-side:
-- GROUP BY: 1 row per user
SELECT user_id, SUM(total) FROM orders GROUP BY user_id;
-- Window: 1 row per order, with user total alongside
SELECT id, user_id, total,
SUM(total) OVER (PARTITION BY user_id)
FROM orders;Each Row, Different Frame
The "frame" is the set of rows within the partition that the function looks at. Default frame with ORDER BY is "from start to current row" — that's what gives you running totals.
Named Windows
If multiple functions share a window, name it once:
SELECT id, user_id, total,
SUM(total) OVER w AS running_sum,
AVG(total) OVER w AS running_avg
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);Most Aggregates Work as Window Functions
COUNT, SUM, AVG, MIN, MAX — all of them can be used over a window. Specialised window functions add ROW_NUMBER, RANK, LAG/LEAD, NTILE, etc.
Window Functions Run After WHERE
Logical order: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY → LIMIT. So WHERE filters before windows are computed.
Performance Considerations
Window functions require sorting (or a hash). For large partitions, an index on (partition, order) columns avoids the sort step.
Recap
Window functions add aggregate values per row.
- OVER () = whole result
- PARTITION BY = group the window
- ORDER BY in window = enable running calcs
Quick Check
What does SUM(x) OVER (PARTITION BY user_id) compute?
Frequently asked questions
Is the “OVER, PARTITION BY, ORDER BY” lesson free?
Yes — the full text of “OVER, PARTITION BY, ORDER BY” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “OVER, PARTITION BY, ORDER BY”?
Understand the window function model: OVER, PARTITION BY (group), ORDER BY (sort), and how it differs from GROUP BY. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “OVER, PARTITION BY, ORDER BY” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- OVER, PARTITION BY, ORDER BY
- ROW_NUMBER, RANK, DENSE_RANK
- LAG, LEAD and Time-Series Patterns
- Running Totals and Moving Averages