Question

USE SQL PROGRAMMING

List the departments that are doing a terrible job of responding to human resources complaints in a timely manner.

Prepare a query that does the following:

1) Query that counts the total number of human resources complaints

2) The quantity of untimely responses

3) Percentage of untimely responses by each department

Use the formula : (untimely responses/ total number of human resources complaints)

4) Display the departments that had at least 150 complaints

5) Show the department that had the highest percentage of untimely responses. Return the department name and percentage of untimely responses.

Answer 1

Im going to suppose there are two tables named 'complaints' and 'departments' that have their own relevant columns. With that in mind here is an example SQL Query.

SQL Query

SELECT

Divisions.divisions_name,

COUNT(complaints.complaint_id) AS total_complaints,

SUM(CASE WHEN complaints.response_time > '24 hours' THEN 1 ELSE 0 END) AS untimely_responses,

(SUM(CASE WHEN complaints.response_time > '24 hours' THEN 1 ELSE 0 END) / COUNT(complaints.complaint_id)) * 100 AS percentage_untimely

FROM

complaints

JOIN

divisions ON complaints.division_id = divisions.division_id

GROUP BY

divisions.division_name

HAVING

COUNT(complaints.complaint_id) >= 150

ORDER BY

percentage_untimely DESC

LIMIT 1;

Step-by-step explanation

1. The first line of the SELECT statement retrieves the department name from the "departments" table.
2. The COUNT function is used to count the total number of human resources complaints.
3. The SUM and CASE statements are used to count the number of untimely responses (response time > 24 hours) for each department.
4. The percentage of untimely responses for each department is calculated using the formula provided in the question.
5. The query groups the results by department name and filters out any departments that have fewer than 150 complaints.
6. The results are sorted in descending order by the percentage of untimely responses and the department with the highest percentage is returned using the LIMIT clause.

Note: This query example assumes that the "response time" column in the "complaints" table has values like "24 hours" that show the length of time. Also, you may need to change the names of the columns or tables to fit your database.