import unittest
import psycopg2
import pandas as pd
from decimal import Decimal

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_column_name(self):
        """Check that result column is named avg_bonus."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        _dynamic_test(
            self,
            len(columns) == 1 and columns[0].lower() == "avg_bonus",
            "The result column is named avg_bonus.",
            f"The result column should be named avg_bonus, but got: {columns}"
        )

    def test_single_row(self):
        """Check that query returns a single row."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        _dynamic_test(
            self,
            len(rows) == 1,
            "Query returns a single row.",
            f"Query should return a single row, but got {len(rows)} rows."
        )

    def test_nulls_treated_as_zero(self):
        """Check that query treats NULL bonus values as zero when averaging."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        user_result = self.cursor.fetchone()[0]
        # Calculate expected average: replace null with 0
        bonuses = [Decimal('1500.00'), Decimal('0'), Decimal('2000.00'), Decimal('1200.00'), Decimal('0'), Decimal('1800.00'), Decimal('1300.00'), Decimal('0'), Decimal('1100.00'), Decimal('0')]
        expected_avg = sum(bonuses) / Decimal(len(bonuses))
        # Allow small floating point error
        condition = abs(user_result - expected_avg) < Decimal('0.01')
        _dynamic_test(
            self,
            condition,
            "Query treats NULL bonuses as zero and calculates correct average.",
            f"Expected average bonus: {expected_avg}, but got: {user_result}"
        )

    def test_uses_coalesce(self):
        """Check that COALESCE is used in the query to handle NULLs."""
        with open("query.sql", "r") as file:
            query = file.read()
        uses_coalesce = "coalesce" in query.lower()
        _dynamic_test(
            self,
            uses_coalesce,
            "Query uses COALESCE to replace NULLs.",
            "Query does not use COALESCE to handle NULL values in bonus column."
        )

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):
    import re
    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

This beginner course focuses on the essential SQL functions used for data cleaning and transformation. Learners will practice handling missing values, formatting and standardizing data, and applying string, date, numeric, logical, and null handling functions to prepare datasets for analysis. The course provides hands-on challenges directly related to the most common data preparation tasks in SQL.

Challenge: Calculate Average Bonus

解答