import unittest
import psycopg2
import pandas as pd
import re

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_correct_category_and_total(self):
        """Check if query returns the correct category and total quantity."""
        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)
        # Compute correct solution
        solution_query = '''
            SELECT p.category, SUM(o.quantity) AS total_quantity
            FROM orders o
            JOIN products p ON o.product_id = p.product_id
            GROUP BY p.category
        '''
        self.cursor.execute(solution_query)
        all_rows = self.cursor.fetchall()
        all_df = pd.DataFrame(all_rows, columns=["category", "total_quantity"])
        max_total = all_df["total_quantity"].max()
        expected_df = all_df[all_df["total_quantity"] == max_total]
        # Compare sets of (category, total_quantity)
        actual_set = set(tuple(x) for x in result_df.values)
        expected_set = set(tuple(x) for x in expected_df.values)
        condition = actual_set == expected_set
        missing = expected_set - actual_set
        extra = actual_set - expected_set
        failure_msg = ""
        if missing:
            failure_msg += f"Missing expected rows: {missing}\\n"
        if extra:
            failure_msg += f"Unexpected extra rows: {extra}\\n"
        _dynamic_test(
            self,
            condition,
            "Returned correct category and total quantity.",
            failure_msg.strip()
        )

    def test_no_subquery_in_from(self):
        """Ensure the query does not use a subquery in the FROM clause (only top-level FROM)."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Remove newlines and compress whitespace for robust parsing
        q = re.sub(r"\s+", " ", query.lower())
        # Find the top-level FROM ...
        from_match = re.search(r"from ", q)
        if from_match:
            from_pos = from_match.end()
            # Look for ( immediately after FROM (allowing whitespace)
            after_from = q[from_pos:].lstrip()
            uses_subquery = after_from.startswith("(")
        else:
            uses_subquery = False
        _dynamic_test(
            self,
            not uses_subquery,
            "Query does not use subqueries in the top-level FROM clause.",
            "Query uses a subquery in the top-level FROM clause, which is not allowed."
        )

    def test_uses_subquery_in_where_or_having(self):
        """Check that the query uses a subquery in the WHERE or HAVING clause."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Look for HAVING ... (SELECT ... ) or WHERE ... (SELECT ... )
        uses_subquery = re.search(r"having[\s\S]*\(\s*select", query, re.IGNORECASE) or re.search(r"where[\s\S]*\(\s*select", query, re.IGNORECASE)
        _dynamic_test(
            self,
            bool(uses_subquery),
            "Query uses a subquery in the WHERE or HAVING clause.",
            "Query does not use a subquery in the WHERE or HAVING clause as required."
        )

    def test_handles_ties(self):
        """Check that the query returns all categories if there is a tie for highest total."""
        # Insert tie situation: set 'Home & Kitchen' to match Electronics
        self.cursor.execute("UPDATE orders SET quantity = 4 WHERE order_id = 2;") # Home & Kitchen
        self.cursor.execute("UPDATE orders SET quantity = 4 WHERE order_id = 8;") # Home & Kitchen
        self.connection.commit()
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        categories = set(r[0] for r in rows)
        # Compute correct solution
        self.cursor.execute('''
            SELECT p.category, SUM(o.quantity) AS total_quantity
            FROM orders o
            JOIN products p ON o.product_id = p.product_id
            GROUP BY p.category
        ''')
        all_rows = self.cursor.fetchall()
        all_df = pd.DataFrame(all_rows, columns=["category", "total_quantity"])
        max_total = all_df["total_quantity"].max()
        expected_categories = set(all_df[all_df["total_quantity"] == max_total]["category"])
        condition = categories == expected_categories
        failure_msg = f"Expected categories with highest total: {expected_categories}, but got: {categories}"
        _dynamic_test(
            self,
            condition,
            "Query correctly returns all tied categories.",
            failure_msg
        )

    def test_aggregates_per_category(self):
        """Check that query aggregates total quantity by category."""
        with open("query.sql", "r") as file:
            query = file.read()
        # The output must have columns: category and total_quantity
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        has_category = any(normalize_text(c) == "category" for c in columns)
        has_total = any(normalize_text(c) == "total_quantity" for c in columns)
        _dynamic_test(
            self,
            has_category and has_total,
            "Query output includes category and total_quantity columns.",
            "Query output must include columns named 'category' and 'total_quantity'."
        )

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

実際のシナリオを通じて、SQLにおけるサブクエリの記述と応用の技術を習得します。本モジュールでは、サブクエリの理論と実践、SELECT、WHERE、FROM句での利用、相関サブクエリについて解説します。

注文、顧客、商品に焦点を当てたeコマースデータセットを使用してサブクエリを探求します。サブクエリを活用してインサイトを抽出し、ビジネス上の課題を解決する方法を学びます。

チャレンジ：最も人気のある商品カテゴリ

解答