import unittest
import psycopg2
import pandas as pd

class TestTask(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_columns_and_order(self):
        """Check that the query selects the correct columns in the correct order."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        expected_columns = ["id", "name", "population", "continent"]
        condition = columns == expected_columns
        _dynamic_test(
            self,
            condition,
            "Selected columns are exactly id, name, population, continent in correct order.",
            f"Selected columns are {columns}, but expected {expected_columns}."
        )

    def test_filter_north_america(self):
        """Check that only rows with continent 'North America' are returned."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        # continent is the 4th column (index 3)
        all_na = all(row[3] == 'North America' for row in rows)
        _dynamic_test(
            self,
            all_na,
            "All rows have continent 'North America'.",
            "There are rows with continent not equal to 'North America'."
        )

    def test_population_descending(self):
        """Check that the result is ordered by population descending."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        populations = [row[2] for row in rows]
        condition = all(populations[i] >= populations[i+1] for i in range(len(populations)-1))
        _dynamic_test(
            self,
            condition,
            "Population column is sorted in descending order.",
            "Population column is not sorted in descending order."
        )

    def test_expected_rows(self):
        """Check that the correct rows are returned for 'North America'."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        user_rows = self.cursor.fetchall()
        user_rows_set = set(user_rows)
        # Expected rows for 'North America' continent
        expected_rows = [
            (8, 'United States', 278357000, 'North America'),
            (3, 'Mexico ', 98881000, 'North America')
        ]
        # Remove trailing spaces from name for comparison
        expected_rows = [ (r[0], r[1].strip(), r[2], r[3]) for r in expected_rows ]
        user_rows_set = set( (r[0], r[1].strip(), r[2], r[3]) for r in user_rows )
        expected_rows_set = set(expected_rows)
        missing = expected_rows_set - user_rows_set
        extra = user_rows_set - expected_rows_set
        condition = len(missing) == 0 and len(extra) == 0
        msg = ""
        if missing:
            msg += f"Missing rows: {missing}\\n"
        if extra:
            msg += f"Unexpected extra rows: {extra}\\n"
        _dynamic_test(
            self,
            condition,
            "Query returns exactly the expected rows for 'North America'.",
            msg.strip()
        )

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

Focuses on practical SQL skills for filtering data with WHERE, sorting results with ORDER BY, and summarizing information using GROUP BY and aggregate functions. Hands-on challenges reinforce each concept, preparing learners to analyze and summarize real-world datasets effectively.

Challenge: North America Population Sort

Ratkaisu