Window Functions for Call Centre Analytics — A Practical PostgreSQL Guide

Ruth Kegicha — Wed, 18 Mar 2026 11:14:24 +0000

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 log of events. You need session durations.

SELECT agent_id, event_time AS login_time, LEAD(event_time) OVER ( PARTITION BY agent_id ORDER BY event_time ) AS next_event_time, LEAD(event_type) OVER ( PARTITION BY agent_id ORDER BY event_time ) AS next_event_type FROM agent_activity_log WHERE event_type = 'LOGIN';

Pair LAG() and LEAD() together and you can reconstruct full session timelines without ever leaving SQL.

Real use case: missed call percentage per site
This one trips people up when you have multiple sites with different in-hours rules and company exclusions.

SELECT site_id, COUNT(*) FILTER (WHERE call_outcome = 'MISSED') AS missed_calls, COUNT(*) AS total_calls, ROUND( COUNT(*) FILTER (WHERE call_outcome = 'MISSED') * 100.0 / NULLIF(COUNT(*), 0), 2 ) AS missed_pct, AVG( COUNT(*) FILTER (WHERE call_outcome = 'MISSED') * 100.0 / NULLIF(COUNT(*), 0) ) OVER () AS avg_missed_pct_all_sites FROM universal_queue WHERE queued_at BETWEEN '2024-01-01' AND '2024-01-31' AND in_hours = true AND company_id NOT IN (/* exclusions */) GROUP BY site_id;

Notice NULLIF(COUNT(*), 0) — always protect against division by zero when calculating percentages on filtered data. And the _AVG(...) OVER () _at the end gives you the cross-site average in the same query without a subquery or CTE.

The three window functions I use most

ROW_NUMBER() — deduplicate events, get the latest record per agent:

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY agent_id ORDER BY event_time DESC ) AS rn FROM agent_activity_log ) t WHERE rn = 1;

_LAG() / LEAD() _— calculate time between events, detect gaps in activity, reconstruct sequences.

SUM() OVER () — running totals across a result set without grouping away your detail rows.

One thing that catches people out

PARTITION BY is not the same as GROUP BY. Group by collapses your rows. Partition by keeps them — the window function just sees a subset of rows for its calculation. You can mix aggregates and window functions in the same query but they run at different stages. If you're getting unexpected results, check whether your WHERE clause is filtering rows before or after the window function runs — it usually is.

Where to go next

If this is new to you, practice with ROW_NUMBER() first — it's the most intuitive and the errors are easy to read. Then move to **LAG()/LEAD() **for anything involving sequences of events. Once those feel natural, RANK(), DENSE_RANK() and NTILE() are just variations on the same idea.

Window functions are one of those SQL features that once you know, you can't imagine working without. Especially when the data is telling a story across time.

DEV Community: Ruth Kegicha

Window Functions for Call Centre Analytics — A Practical PostgreSQL Guide