01 — The ProblemThe CRM has the data; analysts needed the view

Support analysts already had everything they needed technically — it just lived in a CRM interface built for case management, not for triage. What was missing wasn't more data. It was a consolidated, real-time answer to three questions: what needs a response right now, what's been waiting too long without follow-up, and what's the full state of everything still open.

02 — One Parameter, Cloned Per AnalystPortable by design

The notebook is pure SQL, driven by a single string parameter — the analyst's name. Every query in the notebook reads from that one parameter, which makes the whole thing portable: give a new analyst their own clone of the notebook, and there's no code to change, just the parameter to set.

Cloning per analyst instead of sharing one live multi-user notebook was a deliberate choice — it keeps each analyst's execution state fully isolated, so one person running the report doesn't affect another's session or results.

03 — Bridging Two SourcesCurrent status plus full history

The queries layer two data sources together: one gives current, authoritative case status; the other gives the full historical activity record. A UNION pattern bridges the two, and window functions — ROW_NUMBER, QUALIFY — isolate the most recent activity per case without needing subquery nesting to get there.

QUALIFY does in one clause what a nested subquery would take three lines to express. The SQL reads like the question it's answering, not like the plumbing underneath it.

04 — The Analytical LogicThree small decisions that do most of the work

  • Auto-acknowledgment filtering — an inbound email arriving within a short window after an outbound is treated as system-generated and excluded from "needs response" counts. Without this, every auto-reply looks like a customer reply waiting on the analyst.
  • Business day calculation — a pure-SQL weekday arithmetic formula counts only working days since the last analyst outbound, powering the stale-follow-up queue with no UDFs and no calendar table to maintain.
  • Priority flagging — inline CASE expressions derive urgency tiers (overdue, waiting, stale) directly from elapsed time thresholds, so the result set already carries the flag an analyst would otherwise compute by eye.

05 — Why No PythonThe right amount of tooling

The notebook runs entirely on a SQL warehouse, with no Python involved. For a workload-management report driven by one parameter and read directly by the people using it, that's not a limitation — it's the right level of tooling. It's lightweight, fast to load, and legible to a non-engineer who wants to see or tweak the logic.

→ Why this matters

Not every internal tool needs a pipeline. Sometimes the fastest path to something genuinely useful is recognizing that the data's already there, and the actual job is writing a query someone can read.

06 — TakeawaysMatch the tool to the actual ask

  • One parameter can make a whole notebook portable. Don't build configuration you don't need.
  • Filter out system noise at the query level, not by asking analysts to mentally discount auto-replies every time they look at their queue.
  • Window functions replace nested subqueries — QUALIFY in particular keeps "most recent per group" logic legible.
  • SQL-only is a legitimate architecture when the audience is the query itself, not a downstream pipeline.