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

Master the use of built-in SQL functions to manipulate strings, dates, numbers, logical values, nulls, and JSON data. This course provides a hands-on approach to understanding and applying SQL functions in real-world scenarios, enhancing your data querying and transformation skills.

Explore SQL's powerful string functions for manipulating and analyzing textual data in databases.

Master SQL's date and time functions to work with temporal data effectively.

Learn to use SQL's numeric functions for calculations and data analysis.

Harness SQL's logical functions to control query flow and make decisions based on data.

Master handling of NULL values in SQL using specialized functions.

Learn to work with JSON data in SQL using specialized functions for querying and manipulation.

Challenge: Calculate Average Bonus

Solution

Challenge: Calculate Average Bonus

Solution