🛠️ Window functions for exception ageing

💡 Use Case

• Three query patterns Priya Raman taught me on the Thursday after Rotterdam, in forty minutes at her desk, with the comments rewritten in my own words afterwards so that I would have to understand them.
• All three answer versions of one question from the dispatch floor: how old is this exception, compared with the others this dispatcher is looking at.
• They are why the pilot report could say that the list is not only long but old at the bottom, and they were used again in March to check the rewritten rules for 📂 Exceptions Inbox general availability.

How I use it

1. Rank within a group. Number each dispatcher's open exceptions from oldest to newest. Row 1 is the one they have been ignoring longest.
2. Compare with the previous row. For one shipment, put each event next to the one before it and take the difference. That is the gap between it went wrong and somebody looked.
3. Running count. For each hour of the morning, how many are open so far. This draws the list filling up before 09:00.

-- one line per open exception, numbered oldest first, per dispatcher
-- rn = 1 is the oldest thing on that person's list
select dispatcher_id, exception_id, opened_at,
       row_number() over (partition by dispatcher_id order by opened_at) as rn
from open_exceptions

⚖️ Pros & Cons

Pros

• Each replaces a query I had written as three nested ones and could not read a week later.
• Per dispatcher is one phrase in the query and not a separate export per person.
• Priya's own versions are shorter. Mine have comments, and I can explain every line.

Cons

• Easy to get subtly wrong: forget the ordering and the answer is confident nonsense.
• Slow on the full table. I run them on one customer and one week at a time.
• This is the upper edge of ♦️ SQL for product questions for me. Past here I ask.

🔄 Alternatives

• Exporting to a spreadsheet and sorting by hand. Works up to about one dispatcher.
• Asking Priya each time. She would, and she has a platform to run now.