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_group_by_month(self):
        """Check if query groups by month using transaction_date column."""
        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)
        # Should have a 'month' column and only one unique month (2024-06-01)
        has_month_col = 'month' in result_df.columns
        # All values in 'month' should be the same (2024-06-01)
        all_months = set(result_df['month'].astype(str)) if has_month_col else set()
        condition = has_month_col and all_months == {'2024-06-01'}
        _dynamic_test(
            self,
            condition,
            "Query groups by month correctly.",
            "Query must group by month using transaction_date."
        )

    def test_only_sales_category(self):
        """Check if only transactions with category 'Sales' are included."""
        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)
        # Sum of all sales transactions in June 2024
        expected_total = 1500.00 + 2500.00 + 1800.00 + 2200.00 + 500.00 + 1200.00 + 900.00
        # There should be only one row, and total_sales should match expected
        only_one_row = len(result_df) == 1
        total_sales = float(result_df.iloc[0]['total_sales']) if only_one_row and 'total_sales' in result_df.columns else None
        condition = only_one_row and abs(total_sales - expected_total) < 0.01
        _dynamic_test(
            self,
            condition,
            "Query only includes 'Sales' transactions and sums correctly.",
            "Query must include only 'Sales' transactions and sum sales amount."
        )

    def test_returns_expected_columns(self):
        """Check if the result contains the correct columns: month and total_sales."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        has_month = 'month' in columns
        has_total_sales = 'total_sales' in columns
        condition = has_month and has_total_sales
        _dynamic_test(
            self,
            condition,
            "Query returns columns 'month' and 'total_sales'.",
            "Query must return columns 'month' and 'total_sales'."
        )

    def test_no_extra_rows(self):
        """Check that the query returns only one row (since all sales are in June 2024)."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        condition = len(rows) == 1
        _dynamic_test(
            self,
            condition,
            "Query returns one row for the month.",
            "Query must return one row for the month of sales."
        )

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 SQL essentials for finance and accounting by learning to analyze time-series data, perform period-over-period comparisons, execute ledger-style joins, ensure data accuracy, and understand transaction logic. This course is designed for absolute beginners and uses real-world finance scenarios to make learning engaging and practical.

Learn to aggregate and analyze financial data over time, such as daily sales, monthly expenses, and yearly profits, using SQL's aggregation functions and date handling.

Learn to compare financial performance across different periods, such as month-over-month or year-over-year growth, using SQL.

Master ledger-style joins to reconcile accounts, match transactions, and ensure financial data integrity.

Ensure data accuracy, perform reconciliation, and understand transaction logic and ACID properties in SQL for reliable financial operations.

Challenge: Aggregate Monthly Sales

解答