How to Analyze Support Ticket Trends with SQL
Every support tool ships a "reporting" tab. Zendesk has Explore, Intercom has its built-in reports, Freshdesk has Analytics. They all show you the same three numbers: ticket volume, average response time, average resolution time. And they're all slightly wrong in ways you can't inspect because the queries are hidden behind drag-and-drop widgets.
I spent a year trusting the built-in reports before I pulled the raw data into Postgres and ran the numbers myself. The averages didn't match. Turned out the tool was excluding "merged" tickets from resolution time but including them in volume, which made both numbers misleading in opposite directions. That's not a bug anyone would fix for me. The only fix was writing my own queries.
Here's what I run now. Six queries that cover more ground than most reporting tabs, starting from two tables you can export from any ticketing system or model directly if you're building your own.
The schema
-- tickets: one row per support ticket
-- id, customer_id, subject, category, priority, status,
-- assigned_to, created_at, first_response_at, resolved_at
-- ticket_comments: every reply (customer + agent)
-- ticket_id, author_id, is_internal, created_atThat's it. Most ticketing platforms export both. If yours doesn't expose first_response_at as a column, you can derive it from ticket_comments with a min(created_at) WHERE is_internal = false AND author_id != ticket.customer_id subquery. Annoying but straightforward.
1. Volume trends with a 7-day moving average
Raw daily counts are noisy. Mondays spike, weekends drop, holidays create holes. A 7-day moving average smooths that out so you can see whether volume is actually growing or just bouncing around.
WITH daily_volume AS (
SELECT
date_trunc('day', created_at)::date AS day,
count(*) AS tickets_created
FROM tickets
WHERE created_at >= now() - interval '90 days'
GROUP BY 1
)
SELECT
day,
tickets_created,
round(avg(tickets_created) OVER (
ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 1) AS moving_avg_7d
FROM daily_volume
ORDER BY day;If the moving average is climbing while nothing else in the product changed, you probably have a quality regression somewhere. If it's climbing and your user base is growing proportionally, that's fine. The ratio of tickets to active users matters more than the absolute count.
Fastero
Connect your database. Ask questions. Get dashboards.
Postgres, BigQuery, Snowflake, and 10+ sources — live-connected, AI-powered, no dashboard builder learning curve.
Try free →2. Response and resolution time by priority
Median is the right central tendency here, not average. One ticket that sat open for three weeks because the customer went on vacation will wreck your average but barely touch the median. P95 catches the tail that your worst-experience customers actually live in.
SELECT
priority,
count(*) AS ticket_count,
round(percentile_cont(0.5) WITHIN GROUP (
ORDER BY extract(epoch FROM first_response_at - created_at) / 3600
)::numeric, 1) AS median_response_hrs,
round(percentile_cont(0.95) WITHIN GROUP (
ORDER BY extract(epoch FROM first_response_at - created_at) / 3600
)::numeric, 1) AS p95_response_hrs,
round(percentile_cont(0.5) WITHIN GROUP (
ORDER BY extract(epoch FROM resolved_at - created_at) / 3600
)::numeric, 1) AS median_resolution_hrs,
round(percentile_cont(0.95) WITHIN GROUP (
ORDER BY extract(epoch FROM resolved_at - created_at) / 3600
)::numeric, 1) AS p95_resolution_hrs
FROM tickets
WHERE resolved_at IS NOT NULL
AND created_at >= now() - interval '90 days'
GROUP BY priority
ORDER BY
CASE priority
WHEN 'critical' THEN 1 WHEN 'high' THEN 2
WHEN 'medium' THEN 3 WHEN 'low' THEN 4
END;Watch the gap between median and P95. A 2-hour median with a 48-hour P95 means most tickets are handled fast but a meaningful slice of customers waits two days. That tail is where your one-star reviews come from.
3. Repeat contact rate
This is the metric I think matters most, and it's the one almost nobody tracks.
If 30% of your customers who file a ticket come back with another ticket within 30 days, you don't have a support speed problem. You have a product problem. Those repeat contacts mean either the first resolution didn't stick, or the customer has a different issue rooted in the same underlying friction. Speed won't fix that. Fixing the product will.
WITH customer_tickets AS (
SELECT
customer_id,
count(*) AS ticket_count,
min(created_at) AS first_ticket,
max(created_at) AS last_ticket
FROM tickets
WHERE created_at >= now() - interval '90 days'
GROUP BY customer_id
),
repeat_contacts AS (
SELECT customer_id
FROM customer_tickets
WHERE ticket_count >= 2
AND last_ticket - first_ticket <= interval '30 days'
)
SELECT
count(DISTINCT ct.customer_id) AS total_customers_with_tickets,
count(DISTINCT rc.customer_id) AS repeat_customers_30d,
round(100.0 * count(DISTINCT rc.customer_id) /
NULLIF(count(DISTINCT ct.customer_id), 0), 1) AS repeat_contact_rate_pct
FROM customer_tickets ct
LEFT JOIN repeat_contacts rc ON rc.customer_id = ct.customer_id;Break this down by category and you'll find that repeat contacts cluster around two or three issue types. Those are your highest-leverage product fixes, and they won't show up in any response-time report.
4. Backlog and SLA compliance
Two questions, one query. The first part tracks open ticket backlog over time (created minus resolved, cumulative). The second calculates SLA compliance per priority tier, assuming your SLAs are defined as maximum resolution hours.
WITH sla_targets(priority, max_resolution_hrs) AS (
VALUES
('critical', 4),
('high', 24),
('medium', 72),
('low', 168)
),
resolved AS (
SELECT
t.priority,
t.resolved_at,
extract(epoch FROM t.resolved_at - t.created_at) / 3600 AS resolution_hrs,
s.max_resolution_hrs
FROM tickets t
JOIN sla_targets s ON s.priority = t.priority
WHERE t.resolved_at IS NOT NULL
AND t.created_at >= now() - interval '90 days'
)
SELECT
priority,
count(*) AS resolved_tickets,
sum(CASE WHEN resolution_hrs <= max_resolution_hrs THEN 1 ELSE 0 END) AS within_sla,
round(100.0 * sum(CASE WHEN resolution_hrs <= max_resolution_hrs THEN 1 ELSE 0 END) /
NULLIF(count(*), 0), 1) AS sla_compliance_pct
FROM resolved
GROUP BY priority
ORDER BY
CASE priority
WHEN 'critical' THEN 1 WHEN 'high' THEN 2
WHEN 'medium' THEN 3 WHEN 'low' THEN 4
END;SLA compliance above 95% across all tiers usually means your SLAs are too generous. Below 80% on critical tickets means something structural is broken and you need to look at staffing or routing, not just tell agents to type faster.
5. Agent productivity and reopen rate
Tickets resolved per agent per week is the obvious metric. The less obvious one is reopen rate: what percentage of tickets an agent resolves get reopened within 7 days. High throughput with a high reopen rate is worse than moderate throughput with clean resolutions, because every reopen is two tickets worth of work disguised as one.
WITH weekly_resolved AS (
SELECT
assigned_to AS agent,
date_trunc('week', resolved_at)::date AS week,
count(*) AS resolved_count
FROM tickets
WHERE resolved_at IS NOT NULL
AND created_at >= now() - interval '90 days'
GROUP BY 1, 2
),
reopens AS (
SELECT
t.assigned_to AS agent,
count(*) AS reopen_count
FROM tickets t
WHERE t.status = 'open'
AND t.resolved_at IS NOT NULL
AND t.created_at >= now() - interval '90 days'
-- ticket was resolved then reopened
AND EXISTS (
SELECT 1 FROM ticket_comments tc
WHERE tc.ticket_id = t.id
AND tc.created_at > t.resolved_at
AND tc.is_internal = false
)
GROUP BY 1
),
agent_totals AS (
SELECT
agent,
count(*) AS total_resolved
FROM tickets
WHERE resolved_at IS NOT NULL
AND created_at >= now() - interval '90 days'
GROUP BY 1
)
SELECT
wr.agent,
round(avg(wr.resolved_count), 1) AS avg_tickets_per_week,
at.total_resolved,
COALESCE(r.reopen_count, 0) AS reopens,
round(100.0 * COALESCE(r.reopen_count, 0) /
NULLIF(at.total_resolved, 0), 1) AS reopen_rate_pct
FROM weekly_resolved wr
JOIN agent_totals at ON at.agent = wr.agent
LEFT JOIN reopens r ON r.agent = wr.agent
GROUP BY wr.agent, at.total_resolved, r.reopen_count
ORDER BY avg_tickets_per_week DESC;I'd avoid using this as a leaderboard. The agents handling "billing dispute" tickets will always be slower than the agents handling "password reset" tickets, and ranking them side by side without normalizing for category is unfair. Filter by category first, then compare.
6. Tickets and churn: the join nobody runs
Here's the question worth more than the other five combined: do customers who file support tickets churn at a higher rate than customers who don't?
This requires joining your ticket data with subscription data. If both live in the same database, it's a straightforward query. If tickets are in Zendesk and subscriptions are in Stripe, you need to get them into the same place first. (Fastero's cross-source DuckDB store handles this: pull both into DuckDB and join on customer_id or email.)
WITH ticket_counts AS (
SELECT
customer_id,
count(*) AS ticket_count
FROM tickets
WHERE created_at >= now() - interval '180 days'
GROUP BY customer_id
),
customer_status AS (
SELECT
s.customer_id,
CASE WHEN s.canceled_at IS NOT NULL THEN 'churned' ELSE 'active' END AS status
FROM subscriptions s
WHERE s.started_at >= now() - interval '365 days'
)
SELECT
CASE
WHEN tc.ticket_count IS NULL THEN '0 tickets'
WHEN tc.ticket_count = 1 THEN '1 ticket'
WHEN tc.ticket_count <= 3 THEN '2-3 tickets'
ELSE '4+ tickets'
END AS ticket_bucket,
count(*) AS customers,
sum(CASE WHEN cs.status = 'churned' THEN 1 ELSE 0 END) AS churned,
round(100.0 * sum(CASE WHEN cs.status = 'churned' THEN 1 ELSE 0 END) /
NULLIF(count(*), 0), 1) AS churn_rate_pct
FROM customer_status cs
LEFT JOIN ticket_counts tc ON tc.customer_id = cs.customer_id
GROUP BY 1
ORDER BY
CASE
WHEN tc.ticket_count IS NULL THEN 0
WHEN tc.ticket_count = 1 THEN 1
WHEN tc.ticket_count <= 3 THEN 2
ELSE 3
END;In every dataset I've seen, the pattern is the same: customers with 1 ticket churn at roughly the same rate as customers with 0 tickets (sometimes lower, because contacting support means they still care). But customers with 4+ tickets in a 6-month window churn at 2-3x the base rate. That bucket is your early warning system.
Pair this with revenue data from Stripe and you can prioritize by dollar impact, not just ticket count. A high-ticket-count customer on your enterprise plan is a different problem than a high-ticket-count customer on your free tier. Fastero can join Stripe billing data with your ticket data so you see both dimensions in one view.
What to do with all this
Six queries is a lot to run manually every week. The value isn't in the one-time analysis -- it's in watching these numbers change over time and catching regressions early. A few things that make this practical:
Save these as scheduled reports and put them on a dashboard that refreshes automatically. Repeat contact rate climbing? Flag it before the next planning meeting. SLA compliance dropping for critical tickets? Route it to the team lead before it becomes a customer escalation.
Set up alerts on the metrics that matter most. Repeat contact rate above 25% and SLA compliance below 90% on critical are good starting thresholds. You'll calibrate from there.
The ticket-to-churn join is the one I'd prioritize if you only do one thing from this post. Most teams treat support and retention as separate functions with separate dashboards. They aren't. A customer filing their fourth ticket in two months is telling you they're about to leave, and no CSAT survey will surface that as clearly as the raw data.
Try Fastero free — connect your ticket database and your billing data in one place, then ask questions in plain English or paste the SQL above. No credit card required.