Skip to content
Back to challenges
Grain and Fact TypesConcept Challengeintermediate20 min

Escalation duration measures

Model SLA-friendly lag and duration measures on an accumulating support workflow fact.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

fact-table-grain

The primary modeling idea this problem reinforces.

Requirements

5

Business needs the model must satisfy.

Read the concept guide: Fact table grain
Scenario

A support organization tracks tickets that can escalate between queues. Leaders need first-response minutes, resolution minutes, and open-to-resolved timing by queue tier and customer segment.

Why this matters

Milestone timestamps are necessary but not always sufficient for operational reporting. Duration measures make pipeline bottlenecks visible without every dashboard re-deriving the same lag logic.

Requirements
  • Create a support escalation fact where one row represents one ticket escalation workflow.
  • Keep the ticket id queryable on the fact.
  • Add opened, first-response, and resolved milestone timestamps.
  • Add first-response and resolution duration measures.
  • Connect the fact to support queue context through an explicit N:1 relationship.
Read-only starter workspace
Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Support escalation starter workspace

The model users start from in the full workspace.

Entities

2

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.