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_groups_by_sale_date(self):
        """Check if the query groups by sale_date."""
        with open("query.sql", "r") as file:
            query = file.read()
        lowered = normalize_text(query)
        has_group_by = "group by" in lowered and "sale_date" in lowered
        _dynamic_test(
            self,
            has_group_by,
            "Query groups by sale_date.",
            "Query does not group by sale_date."
        )

    def test_counts_sales_per_day(self):
        """Check if the query counts sales per day."""
        with open("query.sql", "r") as file:
            query = file.read()
        lowered = normalize_text(query)
        has_count = "count(" in lowered
        _dynamic_test(
            self,
            has_count,
            "Query counts number of sales per day.",
            "Query does not count number of sales per day."
        )

    def test_filters_days_with_more_than_2_sales(self):
        """Check if only days with more than 2 sales are included in the result."""
        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)
        # All sales_count values should be greater than 2
        if not result_df.empty and "sales_count" in result_df.columns:
            all_gt_2 = (result_df["sales_count"] > 2).all()
        else:
            all_gt_2 = result_df.empty  # If empty, that's okay
        _dynamic_test(
            self,
            all_gt_2,
            "All days in result have more than 2 sales.",
            "Result contains days with 2 or fewer sales."
        )

    def test_column_names(self):
        """Check that result columns are sale_date and sales_count."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        has_sale_date = "sale_date" in columns
        has_sales_count = "sales_count" in columns
        _dynamic_test(
            self,
            has_sale_date and has_sales_count,
            "Result columns are sale_date and sales_count.",
            "Result columns do not include sale_date and sales_count."
        )

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: Daily Sales Summary

解答