Window Functions for Call Centre Analytics — A Practical PostgreSQL Guide

If you've ever tried to calculate agent session durations, track missed call trends across multiple sites, or figure out when an agent went idle — you already know that aggregate functions will only take you so far. Window functions changed how I approach call centre analytics entirely. Here's what I've learned building production dashboards on real call data. What is a window function? A regular aggregate like SUM() or COUNT() collapses rows into one result. A window function performs a calculation across a set of rows related to the current row — without collapsing them. SELECT agent_id, event_time, event_type, LAG(event_time) OVER (PARTITION BY agent_id ORDER BY event_time) AS previous_event FROM agent_activity_log; That single LAG() call gives you the previous event timestamp per agent, ordered by time — no subquery, no self-join. Real use case: calculating agent session duration The classic call centre problem — an agent logs in, takes calls, goes on break, logs out. You have a lo