"PostgreSQL Window Functions Tutorial: App Queries in Practice"
"postgres window functions tutorial for real app features: leaderboards with RANK, running totals with SUM() OVER, moving averages, LAG comparisons, and sessionization — all in single queries."
Leaderboards, running totals, moving averages, "days since the user's last order" — every one of these app features is a single query once you've worked through a postgres window functions tutorial aimed at application code instead of data-science textbooks. Window functions compute a value *across a set of rows* while keeping each row in the result: unlike GROUP BY , they don't collapse anything. This guide builds the five patterns app developers actually ship — rankings, running totals, moving averages, previous-row comparisons, and sessionization — with runnable SQL and the mistakes that waste hours. Last updated: October 4, 2026 What you'll learn The OVER (PARTITION BY ... ORDER BY ...) mental model in one minute Leaderboards with ROW_NUMBER , RANK , and DENSE_RANK — and how ties differ Running totals with SUM() OVER (ORDER BY ...) , plus percent-of-total Moving averages with ROWS BETWEEN frames LAG / LEAD for month-over-month and days-since-last-event Sessionization: turning event streams into sessions with a gaps-and-islands trick Why window functions fail in WHERE , and the ROWS vs RANGE frame trap Window Functions in One Minute: OVER, PARTITION BY, ORDER BY A window function looks like an aggregate with an OVER clause describing the "window" of rows it can see: SELECT user_id, score, RANK() OVER (ORDER BY score DESC) AS rank FROM game_scores; OVER (ORDER BY score DESC) says: order all rows by score, and rank each row within that ordering. Add PARTITION BY to restart the calculation per group — like GROUP BY , but without collapsing rows: -- Rank players *within each game*, keeping every row: SELECT game_id, user_id, score, RANK() OVER (PARTITION BY game_id ORDER BY score DESC) AS game_rank FROM game_scores; That's the whole model:…