SQL Queries for Customer Support Data
Uploaded by
22it95SQL Queries for Customer Support Data
Uploaded by
22it95Common questions
Powered by AIYou can list all agents and their colleagues under the same manager with a LEFT JOIN by using: 'SELECT A.[Agent_name], A.[Manager], B.[Agent_name] AS Colleague FROM [akshitadb].[dbo].[Customer_support_data (1)] A LEFT JOIN [akshitadb].[dbo].[Customer_support_data (1)] B ON A.[Manager] = B.[Manager] AND A.[Agent_name] <> B.[Agent_name];' This query matches agents with other agents (colleagues) managed by the same manager, excluding themselves using 'A.[Agent_name] <> B.[Agent_name]'.
To calculate the total handling time for each agent, you would typically need to SUM the handling times grouped by agents. Assuming the column for time is 'connected_handling_time', the query would be: 'SELECT Agent_name, SUM(connected_handling_time) AS Total_Handling_Time FROM [akshitadb].[dbo].[Customer_support_data (1)] GROUP BY Agent_name;' This sums up the handling times for each agent.
To identify agents with the least response to issues, sort the issue response counts in ascending order by using: 'SELECT Agent_name, COUNT(*) AS issue_responded FROM [akshitadb].[dbo].[Customer_support_data (1)] GROUP BY Agent_name ORDER BY issue_responded ASC;' This query groups by agents and sorts based on the issue response count so you can inspect from the lowest.
To identify the customer support category with the highest number of tickets, use: 'SELECT category, COUNT(*) AS Count FROM [akshitadb].[dbo].[Customer_support_data (1)] GROUP BY category ORDER BY Count DESC;' This query aggregates the ticket count by each category and orders the result in descending order so the top category appears first.
You can determine the number of unique tickets each agent handled by using the SQL query: 'SELECT Agent_name, COUNT(*) AS Ticket_Count FROM [akshitadb].[dbo].[Customer_support_data (1)] GROUP BY Agent_name;' This query groups the data by 'Agent_name' and counts the number of entries for each agent, effectively providing the ticket count per agent.
To determine correlation, first calculate average CSAT scores and ticket counts per agent: 'SELECT Agent_name, AVG([CSAT Score]) AS Average_CSAT, COUNT(*) AS Ticket_Count FROM [akshitadb].[dbo].[Customer_support_data (1)] GROUP BY Agent_name;' Export this data and perform a statistical correlation analysis outside SQL, as SQL cannot directly calculate correlation coefficients.
To list tickets above a specific price threshold, use: 'SELECT Product_category, COUNT(*) AS Ticket_Count FROM [akshitadb].[dbo].[Customer_support_data (1)] WHERE Item_price > specific_value GROUP BY Product_category;' Replace 'specific_value' with the threshold to filter and group tickets based on price and category.
To create a view summarizing the total number of tickets managed by each manager, you can use: 'CREATE VIEW Manager_Ticket_Summary AS SELECT [Manager], COUNT(*) AS Total_Tickets FROM [akshitadb].[dbo].[Customer_support_data (1)] GROUP BY [Manager];' This query forms a view that aggregates ticket counts for each distinct manager.
To select the agent with the highest CSAT Score, use: 'SELECT * FROM [akshitadb].[dbo].[Customer_support_data (1)] WHERE [CSAT Score] = ( SELECT MAX([CSAT Score]) FROM [akshitadb].[dbo].[Customer_support_data (1)] );' This query identifies the maximum CSAT Score and selects records matching that score, effectively selecting the top-performing agent(s).
To find agents who work under the same manager but are not the same person, you can use an INNER JOIN SQL query: 'SELECT A.[Agent_name], A.[Manager], B.[Agent_name] AS Other_Agent, B.[Manager] AS Other_Manager FROM [akshitadb].[dbo].[Customer_support_data (1)] A INNER JOIN [akshitadb].[dbo].[Customer_support_data (1)] B ON A.[Manager] = B.[Manager] AND A.[Agent_name] <> B.[Agent_name];' This query finds different agents (Agent_name) under the same manager, ensuring the agents are not the same by checking 'A.[Agent_name] <> B.[Agent_name]'.