Power BI - Essential SQL Queries for Customer Service

Essential SQL Queries for Customer Service.

Ticket Management

• List all open tickets:

SELECT TicketID, CustomerID, IssueType, Status, CreatedDate
FROM Tickets
WHERE Status = 'Open';

• Average resolution time:

SELECT AVG(DATEDIFF(DAY, CreatedDate, ResolvedDate)) AS AvgResolutionTime
FROM Tickets
WHERE Status = 'Closed';

• Tickets by priority:

SELECT Priority, COUNT(*) AS TicketCount
FROM Tickets
GROUP BY Priority;

---

 Agent Performance

• Tickets handled per agent:

SELECT AgentID, COUNT(TicketID) AS TicketsHandled
FROM Tickets
GROUP BY AgentID;

• Average response time per agent:

SELECT AgentID, AVG(DATEDIFF(MINUTE, CreatedDate, FirstResponseDate)) AS AvgResponseTime
FROM Tickets
GROUP BY AgentID;

• Customer satisfaction by agent:

SELECT AgentID, AVG(SatisfactionScore) AS AvgSatisfaction
FROM Feedback
GROUP BY AgentID;

---

Customer Insights

• Top issues reported by customers:

SELECT IssueType, COUNT(*) AS IssueCount
FROM Tickets
GROUP BY IssueType
ORDER BY IssueCount DESC;

• Repeat customers with multiple tickets:

SELECT CustomerID, COUNT(TicketID) AS TicketCount
FROM Tickets
GROUP BY CustomerID
HAVING COUNT(TicketID) > 3;

• Customer satisfaction by region:

SELECT Region, AVG(SatisfactionScore) AS AvgSatisfaction
FROM Feedback f
JOIN Customers c ON f.CustomerID = c.CustomerID
GROUP BY Region;

---

Operational KPIs

• First contact resolution rate:

SELECT SUM(CASE WHEN ResolutionAtFirstContact = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS FCRRate
FROM Tickets;

• Escalation rate:

SELECT SUM(CASE WHEN Escalated = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS EscalationRate
FROM Tickets;

• Average number of interactions per ticket:

SELECT AVG(InteractionCount) AS AvgInteractions
FROM Tickets;

---

Key Notes

• Indexes: Use on TicketID, CustomerID, AgentID for performance.
• Dashboards: These queries feed KPIs like resolution time, FCR, escalation rate, and satisfaction.
• Best practice: Always validate timestamps (CreatedDate, ResolvedDate) for consistency.

---