In a typical Human Resources database, you may be asked to find the highest paid employee in each department. This is a common scenario where subqueries are invaluable: you need to compare each employee’s salary within their department and return only those who earn the most. The challenge is to write a query that, for every department, identifies the top earner by salary. You have access to the `departments` and `employees` tables, where each employee is linked to a department and has a `salary` value. Your goal is to use subqueries to determine the highest paid employee for each department.

import unittest
import psycopg2
import pandas as pd

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_columns_and_row_count(self):
        """Check that the query returns correct columns and number of rows (one for each highest paid employee per department)."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        expected_columns = ['department_name', 'employee_name', 'salary']
        _dynamic_test(
            self,
            set(expected_columns) == set(columns),
            "Query returns the correct columns.",
            f"Query should return columns: {expected_columns}, but got: {columns}"
        )
        # Check that each department's top salary is present (even if more than one employee has it)
        self.cursor.execute('SELECT COUNT(*) FROM (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id) t')
        expected_min_rows = self.cursor.fetchone()[0]
        _dynamic_test(
            self,
            len(rows) >= expected_min_rows,
            "Query returns at least one row per department.",
            f"Expected at least {expected_min_rows} rows, but got {len(rows)}."
        )

    def test_highest_salary_per_department(self):
        """Check that only the highest paid employee(s) per department are present."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        # Build expected result
        self.cursor.execute("SELECT d.name AS department_name, e.name AS employee_name, e.salary FROM employees e JOIN departments d ON e.department_id = d.department_id")
        all_rows = self.cursor.fetchall()
        all_df = pd.DataFrame(all_rows, columns=['department_name', 'employee_name', 'salary'])
        expected = []
        for dept in all_df['department_name'].unique():
            dept_df = all_df[all_df['department_name'] == dept]
            max_salary = dept_df['salary'].max()
            top_earners = dept_df[dept_df['salary'] == max_salary]
            for _, row in top_earners.iterrows():
                expected.append({'department_name': row['department_name'], 'employee_name': row['employee_name'], 'salary': row['salary']})
        expected_df = pd.DataFrame(expected)
        # Merge to find missing top earners
        merged = pd.merge(expected_df, result_df, on=['department_name', 'employee_name', 'salary'], how='outer', indicator=True)
        missing = merged[merged['_merge'] == 'left_only']
        extra = merged[merged['_merge'] == 'right_only']
        _dynamic_test(
            self,
            missing.empty,
            "All highest paid employees per department are present.",
            f"Missing top earners: {missing[['department_name','employee_name','salary']].to_dict('records')}"
        )
        _dynamic_test(
            self,
            extra.empty,
            "No extra rows are present.",
            f"Extra rows found: {extra[['department_name','employee_name','salary']].to_dict('records')}"
        )

    def test_sorting(self):
        """Check that results are sorted by department name and then employee name."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        sorted_df = result_df.sort_values(['department_name', 'employee_name']).reset_index(drop=True)
        _dynamic_test(
            self,
            result_df.reset_index(drop=True).equals(sorted_df),
            "Results are sorted by department name and employee name.",
            "Results are not sorted correctly by department name and employee name."
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\\s{2,}", " ", text)
    text = re.sub(r"\\s*([,:?])\\s*", r"\\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

Master the art of writing and applying subqueries in SQL through real-world scenarios. This course covers the theory and practice of subqueries, including their use in SELECT, WHERE, and FROM clauses, as well as correlated subqueries. Each section features a unique dataset and practical challenges to reinforce your learning.

Explore subqueries using an e-commerce dataset, focusing on orders, customers, and products. Learn to extract insights and solve business problems using subqueries.

Apply subqueries to HR data, including employees, departments, and salaries. Tackle real-world HR analytics tasks.

Use subqueries to solve problems in a library management system, including books, members, and borrowings.

Challenge: Highest Paid Employee per Department

Lösung

Challenge: Highest Paid Employee per Department

Lösung