Support Ticket Analytics
Build the reporting database for a customer support team in PostgreSQL and write the queries their manager checks every week: how long customers wait for the first reply, how long tickets take to be solved, how many tickets are open and how old they are, and how much work each agent has. You'll model tickets and their events (created, replied, reassigned, solved, reopened), generate realistic data, and handle the tricky parts, such as reopened tickets, only counting business hours, and tickets that are still open. Everything is tested with psql scripts. You'll learn event-based data modeling, time calculations with intervals, percentiles, window functions, PL/pgSQL functions, and writing reports people can trust.
- Estimate
- ~12.5h
- Steps
- 7
- Completed by
- 0
- Proposed by
- codeseed.app
psql · PL/pgSQL
Project roadmap
- 01
Define the metrics
~1.5hWrite README.md with exact definitions: first response time (from creation to the first agent reply, ignoring automatic messages), resolution time (from creation to the final solve, with reopened tickets counted until their last solve), backlog aging (open tickets grouped by age: under 1 day, 1 to 3, 3 to 7, over 7), and agent workload (open tickets and replies per agent per week). Decide whether times use business hours. Done when: every metric has a precise definition with an example.
- 02
Design the schema
~1.5hWrite schema.sql with agents, customers, tickets (id, customer, priority, channel, created at, current status and current assignee) and ticket_events (ticket, type created, customer_message, agent_reply, auto_reply, assigned, solved or reopened, the agent, occurred at). Add constraints and indexes on (ticket_id, occurred_at). Done when: the schema loads without errors.
- 03
Generate realistic data
~2hWrite a PL/pgSQL function that creates 3 months of tickets for 10 agents: more tickets on weekdays, replies after realistic delays, reassignments, about 10% reopened tickets, some auto-replies, and some tickets still open. Done when: the data loads, and quick counts show all these cases.
- 04
Calculate response and resolution times
~2.5hWrite views for first response time and resolution time per ticket, following your definitions (using MIN with FILTER and window functions over events). Then report them per week and priority with the median and 90th percentile using percentile_cont. If you use business hours, write a function that counts only working hours between two times. Done when: the times for a few tickets checked by hand are correct.
- 05
Report backlog and workload
~2hWrite a view for the current backlog grouped by age bucket and priority, and a function backlog_at(date) that shows what the backlog was on any past day, rebuilt from the events. Write an agent workload report per week: tickets assigned, replies sent, tickets solved and the median first response time. Done when: backlog_at for today matches the current open tickets.
- 06
Handle edge cases
~1.5hCheck and handle: tickets with no reply yet (they must appear as waiting, not be dropped), tickets solved without any reply, reopened tickets, events with the same timestamp, and reassigned tickets (credit the agent who replied). Document each decision in README.md. Done when: each case is covered by the views and documented.
- 07
Test with psql
~1.5hWrite test.sql that, inside a transaction, inserts a handful of hand-made tickets with known answers (one reopened, one never answered, one reassigned), checks every view against the expected values, raising an error on any difference, then rolls back. Done when: psql -f test.sql runs without errors.
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack