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.
Top comments (0)