Loading Depth Study
Preparing your workspace
Aggregate functions summarize groups of rows, while window functions calculate values across related rows without collapsing them into one row per group.
COUNT, SUM, AVG, MIN, and MAX are useful for grouped reports. PostgreSQL also provides powerful window functions such as ROW_NUMBER, RANK, DENSE_RANK, and running totals. A key difference is that GROUP BY reduces rows into groups, while window functions preserve the individual rows while adding calculated information. These features are extremely useful for analytics and reporting systems.
An admin dashboard can rank students by score within each course while still displaying each student's individual result.
SELECT
course_id,
student_id,
score,
RANK() OVER (
PARTITION BY course_id
ORDER BY score DESC
) AS course_rank
FROM enrollments;Each student receives a rank within their course based on score.