PARTITION BY
PARTITION BY divides the result set into groups (partitions) for window functions - like GROUP BY but without collapsing rows. Each partition gets its own window calculation. This enables per-group rankings, running totals, and comparative analytics.
PARTITION BY Patterns
- Without PARTITION BY: window covers all rows
- With PARTITION BY col: restarts window for each distinct col value
- Combine with ORDER BY: rank within each partition
- Multiple PARTITION BY columns: PARTITION BY col1, col2
- Window aggregates with PARTITION BY: SUM, AVG, COUNT per group - without GROUP BY collapsing rows
- ROWS BETWEEN - define exact window frame (covered in LEAD/LAG)
PARTITION BY Examples
-- Total spent per user as a column (without GROUP BY)
SELECT
id AS order_id,
user_id,
total_amount,
SUM(total_amount) OVER (PARTITION BY user_id) AS user_total,
total_amount / SUM(total_amount) OVER (PARTITION BY user_id) * 100 AS pct_of_user_spend
FROM orders;
-- Compare each order to user's average
SELECT
id,
user_id,
total_amount,
ROUND(AVG(total_amount) OVER (PARTITION BY user_id), 2) AS user_avg,
total_amount - AVG(total_amount) OVER (PARTITION BY user_id) AS vs_avg
FROM orders;
-- Count orders per user alongside each order row
SELECT
id,
user_id,
status,
COUNT(*) OVER (PARTITION BY user_id) AS user_order_count
FROM orders;
-- Running total WITHIN each user (PARTITION BY user_id + ORDER BY)
SELECT
id,
user_id,
created_at,
total_amount,
SUM(total_amount) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS user_running_total
FROM orders;Quick Quiz
Tip
Tip
Practice PARTITION BY in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Eliminate redundancy - each fact stored once. 3NF covers most real-world needs.
Common Mistake
Warning
A common mistake with PARTITION BY is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of PARTITION BY from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- PARTITION BY divides the result set into groups (partitions) for window functions - like GROUP BY but without collapsing rows.
- Without PARTITION BY: window covers all rows
- With PARTITION BY col: restarts window for each distinct col value
- Combine with ORDER BY: rank within each partition