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_query_returns_single_row_and_two_columns(self):
        """Check if query returns exactly one row and two columns."""
        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]
        condition = (len(rows) == 1) and (len(columns) == 2)
        _dynamic_test(
            self,
            condition,
            "Query returns a single row with two columns.",
            f"Query should return one row with two columns, got {len(rows)} row(s) and {len(columns)} column(s)."
        )

    def test_total_sales_is_correct(self):
        """Check if total_sales column correctly counts rows in sales table."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("SELECT COUNT(*) FROM sales;")
        expected_total_sales = self.cursor.fetchone()[0]
        self.cursor.execute(query)
        row = self.cursor.fetchone()
        # Try both column names and index
        if isinstance(row, dict):
            user_total_sales = row.get('total_sales')
        else:
            user_total_sales = row[0]
        condition = (user_total_sales == expected_total_sales)
        _dynamic_test(
            self,
            condition,
            f"total_sales value is correct: {expected_total_sales}.",
            f"total_sales value is incorrect. Expected {expected_total_sales}, got {user_total_sales}."
        )

    def test_total_quantity_is_correct(self):
        """Check if total_quantity column correctly sums the quantity column."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("SELECT SUM(quantity) FROM sales;")
        expected_total_quantity = self.cursor.fetchone()[0]
        self.cursor.execute(query)
        row = self.cursor.fetchone()
        # Try both column names and index
        if isinstance(row, dict):
            user_total_quantity = row.get('total_quantity')
        else:
            user_total_quantity = row[1]
        condition = (user_total_quantity == expected_total_quantity)
        _dynamic_test(
            self,
            condition,
            f"total_quantity value is correct: {expected_total_quantity}.",
            f"total_quantity value is incorrect. Expected {expected_total_quantity}, got {user_total_quantity}."
        )

    def test_column_names(self):
        """Check if columns are named total_sales and total_quantity (case-insensitive)."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0].lower() for desc in self.cursor.description]
        expected_columns = ["total_sales", "total_quantity"]
        condition = all(col in columns for col in expected_columns)
        missing = [col for col in expected_columns if col not in columns]
        _dynamic_test(
            self,
            condition,
            "Columns are correctly named.",
            f"Missing columns: {', '.join(missing)}."
        )


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

A comprehensive beginner-friendly course designed to teach SQL from the ground up, focusing on practical data analysis skills. Through engaging explanations, real-world examples, and hands-on challenges, learners will master querying, transforming, and analyzing data using SQL.

Learn the foundational concepts of relational databases and SQL. Explore how to retrieve, filter, and sort data from tables, setting the stage for deeper data analysis.

Dive into SQL's powerful aggregation features. Learn to summarize, group, and analyze data for deeper insights.

Unlock the power of combining data from multiple tables. Learn to perform different types of joins to answer complex business questions.

Master advanced SQL techniques for powerful data analysis, including subqueries, window functions, and data transformation.

Challenge: Total Sales Calculation

解答