Real-world recipes · Operations analytics

Ticket resolution time and throughput

In Excel, you'd reach for

fx=AVERAGEIFS over a column of (closed - opened) * 24

Two numbers every ops team watches: how long work takes (resolution time) and how much gets done (throughput).

julianday() turns a timestamp into a day count, so subtracting two of them gives days; times 24 gives hours. Open tickets have no closed_at, so we leave them out.

Try itEdit the query, then Run

priority	closed_tickets	avg_hours_to_close
urgent	83	2.2
normal	200	15.4
low	206	43.5

3 rows

Averages hide slow outliers. Many teams also report the share closed within a target, for example AVG(CASE WHEN hours <= 24 THEN 1.0 ELSE 0 END) for "% closed within a day".

Your turn

Throughput: how many tickets did each agent close? Column closed, most first.

This page on one card

Ticket resolution time and throughput: the Excel way (=AVERAGEIFS over a column of (closed - opened) * 24) and the SQL query, side by side — Ticket resolution time and throughput, on one card: the Excel way and the SQL. Download · Save to Pinterest

The practice data (7 tables)

customers: one row per customer
subscriptions: one row per paid plan
events: one row per thing someone did
orders: one row per order
products: a lookup table
campaigns: one row per channel per month
tickets: one row per support ticket

See every column and sample rows