Support-ticket operations reporting
Tidewater Telecom (fictional)
Clean a helpdesk export, measure resolution time against priority-based SLA targets and build a weekly SLA report per team.
- Level
- intermediate
- Time
- ~60 min
- Deliverables
- 2
- Rubric
- v1
Access
Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.
Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).
Business goal
Give the support manager a weekly report of tickets, resolutions and SLA attainment per team, plus the ticket-level facts behind it, refreshed from each new export.
Tidewater's helpdesk exports tickets as CSV. Each ticket has a priority with a resolution target (P1 four hours up to P4 three days), the agent who owns it and, once closed, a resolution time. QA engineers sometimes create test tickets in production, marked [TEST] in the subject. Agents' teams come from the workforce list, which only knows lower-case logins.
Graded deliverables
1. Ticket Facts
55 pointsOne row per real ticket with clean priority, team, resolution hours and SLA result.
- Columns: TicketID, Priority, Team, CreatedAt, ResolvedAt, ResolutionHours, SLAMet
- No test tickets
- ResolutionHours and SLAMet are null exactly for open tickets
- Team never null (Unassigned)
Expected output schema for Ticket Facts Column Type TicketID text Priority text Team text CreatedAt datetime ResolvedAt datetime or null ResolutionHours decimal or null SLAMet logical or null - Grain:
- One row per ticket that isn't a test ticket.
- Order:
- Not graded.
- Nulls:
- ResolvedAt, ResolutionHours and SLAMet are null for open tickets only.
2. Weekly SLA Report
45 pointsTickets, resolutions and SLA attainment per Monday-based week and team.
Builds on: Ticket Facts
- Columns: WeekStart, Team, Tickets, Resolved, MetSLA, SLARate
- Weeks start on Monday
- SLARate null (not 0) when nothing was resolved
- Sorted by WeekStart, then Team
Expected output schema for Weekly SLA Report Column Type WeekStart date (Monday) Team text Tickets whole number Resolved whole number MetSLA whole number SLARate decimal (0–1, 3 dp) or null - Grain:
- One row per week and team with at least one ticket created that week.
- Order:
- Graded: WeekStart ascending, then Team ascending.
- Nulls:
- SLARate is null only when Resolved is 0.