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_selects_order_id_and_order_date(self):
        """Check if order_id and order_date columns are present in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        _dynamic_test(
            self,
            'order_id' in columns and 'order_date' in columns,
            "Result contains both 'order_id' and 'order_date' columns.",
            "Result does not contain both 'order_id' and 'order_date' columns."
        )

    def test_days_since_order_column_present(self):
        """Check if days_since_order column is present in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        _dynamic_test(
            self,
            'days_since_order' in columns,
            "Result contains 'days_since_order' column.",
            "Result does not contain 'days_since_order' column."
        )

    def test_days_since_order_is_correct(self):
        """Check if days_since_order is the difference between CURRENT_DATE and order_date for each row."""
        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)

        # Get current date from database for consistency
        self.cursor.execute("SELECT CURRENT_DATE;")
        current_date = self.cursor.fetchone()[0]

        self.cursor.execute("SELECT order_id, order_date FROM orders;")
        orders = self.cursor.fetchall()
        expected = {}
        for order_id, order_date in orders:
            expected[order_id] = (current_date - order_date).days

        mismatch = []
        for _, row in result_df.iterrows():
            oid = row['order_id']
            days = row['days_since_order']
            if oid in expected and days != expected[oid]:
                mismatch.append((oid, days, expected[oid]))
        condition = len(mismatch) == 0
        if not condition:
            mismatch_str = "\\n".join(f"order_id={oid}: got {got}, expected {exp}" for oid, got, exp in mismatch)
            failure_msg = f"Incorrect days_since_order values:\\n{mismatch_str}"
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "days_since_order values are correct.",
            failure_msg
        )

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

Master the use of built-in SQL functions to manipulate strings, dates, numbers, logical values, nulls, and JSON data. This course provides a hands-on approach to understanding and applying SQL functions in real-world scenarios, enhancing your data querying and transformation skills.

Explore SQL's powerful string functions for manipulating and analyzing textual data in databases.

Master SQL's date and time functions to work with temporal data effectively.

Learn to use SQL's numeric functions for calculations and data analysis.

Harness SQL's logical functions to control query flow and make decisions based on data.

Master handling of NULL values in SQL using specialized functions.

Learn to work with JSON data in SQL using specialized functions for querying and manipulation.

Challenge: Calculate Order Age

Solution

Challenge: Calculate Order Age

Solution