You have obtained the quantity of products in each category. Now, the company tasked you to retrieve the categories where the products cost more than a certain amount of money, so they can put a discount on them. 

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_category_names_with_price_above_450(self):
        """Check that only unique category names with products priced above 450 are returned."""
        with open("query.sql", "r") as file:
            query = file.read()

        # Get the expected result using reference query
        reference_query = '''
            SELECT DISTINCT category.name
            FROM category
            JOIN product ON category.id = product.category_id
            WHERE price > 450
        '''
        self.cursor.execute(reference_query)
        expected_rows = self.cursor.fetchall()
        expected_set = set(row[0] for row in expected_rows)

        # Run user's query
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        user_set = set(row[0] for row in rows)

        condition = user_set == expected_set
        missing = expected_set - user_set
        extra = user_set - expected_set
        fail_msg = ""
        if missing:
            fail_msg += f"Missing expected categories: {', '.join(missing)}. "
        if extra:
            fail_msg += f"Unexpected categories returned: {', '.join(extra)}."
        _dynamic_test(
            self,
            condition,
            "Correct unique category names are returned for products with price > 450.",
            fail_msg or "Incorrect category names returned."
        )

    def test_output_only_category_name_column(self):
        """Check that only one column (category.name) is returned."""
        with open("query.sql", "r") as file:
            query = file.read()

        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        condition = len(columns) == 1 and normalize_text(columns[0]) == "name"
        _dynamic_test(
            self,
            condition,
            "Only one column 'name' is returned.",
            f"Expected only one column named 'name', got: {columns}"
        )

    def test_no_duplicate_category_names(self):
        """Check that there are no duplicate category names in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        names = [row[0] for row in rows]
        condition = len(names) == len(set(names))
        _dynamic_test(
            self,
            condition,
            "No duplicate category names in the result.",
            "Duplicate category names found in the result."
        )

    def test_no_category_with_price_450_or_below(self):
        """Ensure no category is returned where all products have price <= 450."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        returned_names = set(row[0] for row in rows)
        # All categories that have at least one product with price > 450
        self.cursor.execute('''
            SELECT DISTINCT category.name
            FROM category
            JOIN product ON category.id = product.category_id
            WHERE price > 450
        ''')
        valid_names = set(row[0] for row in self.cursor.fetchall())
        # All categories that have only products with price <= 450
        self.cursor.execute('''
            SELECT DISTINCT category.name
            FROM category
            WHERE category.name NOT IN (
                SELECT DISTINCT category.name
                FROM category
                JOIN product ON category.id = product.category_id
                WHERE price > 450
            )
        ''')
        invalid_names = set(row[0] for row in self.cursor.fetchall())
        found_invalid = returned_names & invalid_names
        condition = not found_invalid
        _dynamic_test(
            self,
            condition,
            "No category with only products priced 450 or below is returned.",
            f"Categories returned with only products priced 450 or below: {', '.join(found_invalid)}"
        )


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

This course is perfect for those who already have a basic understanding of SQL and want to delve into more advanced concepts to craft more powerful queries. Throughout the course, you will become familiar with data grouping and filtering grouped data. You will also learn how to work with multiple tables simultaneously, including how to combine them. Additionally, you will explore different types of table joins and how to apply them in practice.

Challenge: Get Category by Product Price

Brief Instructions

解答