Implementation idea of ​​removing the highest and lowest average scores in Mysql satisfaction survey group

Scene description

We have an employee satisfaction survey system with a table in the database:

survey_scoresTable: Store employee satisfaction scores with the company.

The table structure is as follows:

-- Survey rating table
CREATE TABLE survey_scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY,
    department VARCHAR(50), -- department
    employee_id INT,        -- staffID
    score DECIMAL(5, 2)     -- Satisfaction score（0-10point）
);

Insert some test data:

-- Insert survey scoring data
INSERT INTO survey_scores (department, employee_id, score) VALUES
('HR', 1, 8.5),
('HR', 2, 9.0),
('HR', 3, 7.0),
('HR', 4, 8.0),
('HR', 5, 9.5),
('Engineering', 6, 7.5),
('Engineering', 7, 8.0),
('Engineering', 8, 6.5),
('Engineering', 9, 9.0),
('Engineering', 10, 8.5),
('Sales', 11, 6.0),
('Sales', 12, 7.0),
('Sales', 13, 6.5),
('Sales', 14, 8.0),
('Sales', 15, 7.5);

Interview questions

Write an SQL query to implement the following requirements:

• By department (department) Grouping, removing the highest and lowest scores for each department.
• Calculate the average score for each department (after removing the highest and lowest scores).
• Returns the following fields:
  • department(department)
  • avg_score(The average score after the highest score and the lowest score are removed)

Expected results

The query results for the above data are similar to:

HR department：

• Original score:[7.0, 8.0, 8.5, 9.0, 9.5]
• After removing the highest score (9.5) and lowest score (7.0):[8.0, 8.5, 9.0]
• Average score:(8.0 + 8.5 + 9.0) / 3 = 8.17

Problem-solving ideas

Mark the highest and lowest scores for each department：

• Using window functionsROW_NUMBER()Scores for each department are ranked in ascending and descending order, marking the highest and lowest scores.

Filter out the highest and lowest scores for each department：

• Use subqueries or CTEs (Common Table Expressions) to filter out the highest and lowest scores for each department.

Calculate the average score for each department：

• The filtered data are grouped by department and the average score is calculated.

SQL implementation

-- first step：Mark the highest and lowest scores for each department
WITH ranked_scores AS (
    SELECT
        department,
        score,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY score ASC) AS asc_rank,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY score DESC) AS desc_rank
    FROM
        survey_scores
)
-- Step 2：Filter out the highest and lowest scores for each department，Calculate the average score
SELECT
    department,
    AVG(score) AS avg_score
FROM
    ranked_scores
WHERE
    asc_rank > 1 AND desc_rank > 1 -- Remove the highest and lowest scores
GROUP BY
    department;

Query results

The above data, query results:

Inspection points

• Window Functions：
  • useROW_NUMBER()Rank the scores of each department.
• Subquery and CTE (Common Table Expressions)：
  • useWITHThe clause breaks up complex queries into multiple steps to improve readability.

Consider mysql5.7 version

Window functions and CTS are not supported

Implementation ideas

• Find the highest and lowest scores for each department：
  • useGROUP BYandMAX()、MIN()The aggregation function finds the highest and lowest scores for each department.
• Filter out the highest and lowest scores for each department：
  • Use a subquery to compare the raw data with the highest and lowest scores, excluding these scores.
• Calculate the average score for each department：
  • The filtered data is grouped by department and the average score is calculated.

This is the article about Mysql satisfaction survey grouping to remove the highest and lowest average scores. For more related mysql content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!