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. |
-- Write your query for task 1 in this cell
SELECT
id,
COALESCE(customer_id, 0) AS customer_id,
COALESCE(category, 'Other') AS category,
CASE
WHEN status IS NULL THEN 'Resolved'
WHEN status = '-' THEN 'Resolved'
ELSE status
END AS status,
COALESCE(creation_date, '2023-01-01'::DATE) AS creation_date,
COALESCE(response_time, 0) AS response_time,
COALESCE(ROUND(NULLIF(regexp_replace(resolution_time, '[^0-9.]', '', 'g'), '')::NUMERIC, 2), 0) AS resolution_time
FROM public.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.
-- Write your query for task 2 in this cell
SELECT category,
ROUND(MIN(response_time),2) AS min_response,
ROUND(MAX(response_time),2) AS max_response
FROM public.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 s2.rating AS rating,
s1.customer_id AS customer_id,
s1.category AS category,
s1.response_time AS response_time
FROM public.support s1
JOIN public.survey s2
ON s2.id = s1.id OR s2.customer_id=s1.customer_id
WHERE s1.category IN ('Bugs', 'Installation Problem');SELECT
column_name,
data_type,
STRING_AGG(CONCAT(table_schema, '.', table_name), ', ') AS table_list
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name IN ('survey', 'support')
GROUP BY column_name, data_type
HAVING COUNT(*) > 1;