Practical Exam: Sample SQL Associate
Tech Solutions Inc. is a leading technology company specializing in software development and IT consulting services. The company prides itself on delivering innovative solutions to clients across various industries. With a dedicated team of skilled professionals, TechSolutions has earned a reputation for excellence in the tech industry.
Tech Solutions Inc. has been experiencing a decline in customer satisfaction ratings over the past few months. Customer feedback surveys and support tickets indicate an increase in dissatisfaction among clients. The company is concerned about this trend as it directly impacts customer retention, reputation, and overall business growth.
You are working with the customer support team to provide data to managers to help the company take proactive measures to address these concerns effectively.
Data
The following schema diagram shows the tables available.
Task 1
Before you can start any analysis, you need to confirm that the data is accurate and reflects what you expect to see.
It is known that there are some issues with the support table, and the data team have provided the following data description.
Write a query to return data matching this description. You must match all column names and description criteria.
| Column Name | Criteria |
|---|---|
| id | Discrete. The unique identifier of the support ticket. Missing values are not possible due to the database structure. |
| customer_id | Discrete. The unique identifier of the customer. Missing values should be replaced with 0. |
| category | Nominal. The gategory of the support request, can be one of Feedback, Billing Enquiry, Bug, Installation Problem, Other. Missing values should be replaced with Other. |
| status | Nominal. The current status of the support ticket, one of Open, In Progress or Resolved. Missing values should be replaced with 'Resolved'. |
| creation_date | Discrete. The date the ticket was created. Can be any date in 2023. Missing values should be replaced with 2023-01-01. |
| response_time | Discrete. The number of days taken to respond to the support ticket. Missing values should be replaced with 0. |
| resolution_time | Continuos. The number of hours taken to resolve the support ticket, rounded to 2 decimal places. Missing values should be replaced with 0. |
SELECT
id,
COALESCE(customer_id, 0) AS customer_id,
CASE WHEN category IS NULl OR category NOT IN ('Feedback', 'Billing Enquiry', 'Bug', 'Installation Problem', 'Other') THEN 'Other' ELSE category END AS category,
CASE WHEN status IS NULL OR status NOT IN ('Open','In Progress''Resolved') THEN 'Resolved' ELSE status END AS status,
CASE WHEN creation_date IS NULL OR creation_date::DATE < '2023-01-01' OR creation_date::DATE >'2023-12-31' THEN '2023-01-01' ELSE creation_date::date END AS creation_date,
COALESCE(response_time, 0) AS response_time,
ROUND(COALESCE(LEFT(resolution_time, LENGTH(resolution_time)-6)::numeric,0),2) AS resolution_time
FROM
support;
Task 2
It is suspected that the response time to tickets is a big factor in unhappiness.
Calculate the minimum and maximum response time for each category of support ticket.
Your output should include the columns category, min_response and max_response.
Values should be rounded to two decimal places where appropriate.
SELECT category,
ROUND(MIN(response_time),2) as min_response,
ROUND(MAX(response_time),2) as max_response FROM support GROUP BY category;
Task 3
The support team want to know more about the rating provided by customers who reported Bugs or Installation Problems.
Write a query to return the rating from the survey, the customer_id, category and response_time of the support ticket, for the customers & categories of interest.
Use the original support table, not the output of task 1.
SELECT sur.rating, sup.customer_id, sup.category, sup.response_time FROM survey sur
INNER JOIN support sup ON sur.customer_id=sup.customer_id
WHERE sup.category IN ('Bug', 'Installation Problem');