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_all_columns_and_price_tier(self):
        """Check that all original columns and the price_tier column are present."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        expected = set(["id", "product", "quantity", "price", "region", "price_tier"])
        condition = expected.issubset(set(columns))
        _dynamic_test(
            self,
            condition,
            "All required columns including price_tier are present.",
            f"Missing columns: {expected - set(columns)}"
        )

    def test_premium_price_tier(self):
        """Check that price_tier is 'Premium' for price > 700."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        premium_rows = df[df['price'] > 700]
        if not premium_rows.empty:
            condition = all(premium_rows['price_tier'] == 'Premium')
        else:
            condition = True
        _dynamic_test(
            self,
            condition,
            "All price > 700 rows are labeled 'Premium'.",
            "Some price > 700 rows are not labeled 'Premium'."
        )

    def test_standard_price_tier(self):
        """Check that price_tier is 'Standard' for price between 300 and 700 inclusive."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        standard_rows = df[(df['price'] >= 300) & (df['price'] <= 700)]
        if not standard_rows.empty:
            condition = all(standard_rows['price_tier'] == 'Standard')
        else:
            condition = True
        _dynamic_test(
            self,
            condition,
            "All price between 300 and 700 rows are labeled 'Standard'.",
            "Some price between 300 and 700 rows are not labeled 'Standard'."
        )

    def test_budget_price_tier(self):
        """Check that price_tier is 'Budget' for price < 300."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        budget_rows = df[df['price'] < 300]
        if not budget_rows.empty:
            condition = all(budget_rows['price_tier'] == 'Budget')
        else:
            condition = True
        _dynamic_test(
            self,
            condition,
            "All price < 300 rows are labeled 'Budget'.",
            "Some price < 300 rows are not labeled 'Budget'."
        )


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 course designed to help you master the CASE WHEN statement in SQL, from basic conditional logic to advanced use cases in data analysis and reporting. Through engaging theory and real-world tasks, you'll learn to write expressive, powerful queries that adapt to complex business requirements.

Learn the basics of the CASE WHEN statement, its syntax, and how to use it for simple conditional logic in SQL queries.

Delve deeper into complex CASE WHEN expressions, including nested conditions and multiple criteria.

Apply CASE WHEN in WHERE and ORDER BY clauses to filter and sort data conditionally.

Combine CASE WHEN with aggregate functions for advanced reporting and analytics.

Challenge: Price Tier Assignment

解答