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_completed_orders_column(self):
        """Check that completed_orders column returns correct count of 'Delivered' orders."""
        with open("query.sql", "r") as file:
            queries = file.read().strip().split(';')
        # First query
        self.cursor.execute(queries[0])
        row = self.cursor.fetchone()
        columns = [desc[0] for desc in self.cursor.description]
        condition = 'completed_orders' in columns and row[0] == 3
        _dynamic_test(
            self,
            condition,
            "Query returns correct completed_orders count (3) and column name.",
            f"Expected completed_orders = 3, got {row[0] if row else None}. Column present: {'completed_orders' in columns}."
        )

    def test_high_value_sales_column(self):
        """Check that high_value_sales column returns correct sum for orders with total_amount > 500."""
        with open("query.sql", "r") as file:
            queries = file.read().strip().split(';')
        # Second query
        self.cursor.execute(queries[1])
        row = self.cursor.fetchone()
        columns = [desc[0] for desc in self.cursor.description]
        # Only orders 1 and 3 have total_amount > 500: 749.98 + 1199.99 = 1949.97
        expected_sum = 749.98 + 1199.99
        # Allow a small tolerance for float comparison
        value = float(row[0] or 0)
        condition = (
            'high_value_sales' in columns and 
            abs(value - expected_sum) < 0.01
        )
        _dynamic_test(
            self,
            condition,
            "Query returns correct high_value_sales sum (1949.97) and column name.",
            f"Expected high_value_sales = {expected_sum}, got {row[0] if row else None}. Column present: {'high_value_sales' in columns}."
        )

    def test_queries_do_not_return_extra_columns(self):
        """Check that only the expected columns are returned for each query."""
        with open("query.sql", "r") as file:
            queries = file.read().strip().split(';')
        # First query
        self.cursor.execute(queries[0])
        columns1 = [desc[0] for desc in self.cursor.description]
        # Second query
        self.cursor.execute(queries[1])
        columns2 = [desc[0] for desc in self.cursor.description]
        condition = (columns1 == ['completed_orders']) and (columns2 == ['high_value_sales'])
        _dynamic_test(
            self,
            condition,
            "Each query returns only the expected column.",
            f"First query columns: {columns1}. Second query columns: {columns2}."
        )

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 SQL from the ground up with a focus on business intelligence. Learn to write powerful queries for KPIs, implement business logic, analyze trends with window functions, design data models for BI tools, and apply reporting patterns. Each section blends engaging theory with hands-on, real-world challenges to prepare you for BI analyst roles.

Learn to use SQL aggregations to calculate and interpret key business metrics, such as revenue, customer counts, and averages, essential for BI analysis.

Implement business rules and conditional logic in SQL queries using CASE WHEN, enabling dynamic categorization and decision-making.

Analyze business trends and rankings over time using advanced SQL window functions, essential for BI trend analysis and leaderboards.

Challenge: Custom KPIs with CASE WHEN

解答