Yrityksen tulee selvittää **alin hinta** jokaisesta tuotekategoriasta markkinointikampanjaa varten. Tämä tehtävä on annettu sinulle!

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_column_aliases(self):
        """Check if column aliases category_name and min_price are used."""
        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,
            "category_name" in columns and "min_price" in columns,
            "Both column aliases 'category_name' and 'min_price' are present.",
            "Column aliases 'category_name' and/or 'min_price' are missing."
        )

    def test_distinct_product_count(self):
        """Check that only categories with more than 5 distinct products appear."""
        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)

        # Count distinct products per category from raw data
        self.cursor.execute('''
            SELECT category.name, COUNT(DISTINCT product.id) AS product_count
            FROM category
            JOIN product ON category.id = product.category_id
            GROUP BY category.name
        ''')
        prod_counts = dict(self.cursor.fetchall())
        result_categories = set(result_df['category_name'])
        failed = [cat for cat in result_categories if prod_counts.get(cat, 0) <= 5]
        _dynamic_test(
            self,
            len(failed) == 0,
            "All returned categories have more than 5 distinct products.",
            f"The following categories do not have more than 5 distinct products: {failed}"
        )

    def test_expected_result(self):
        """Check that the result matches the expected output."""
        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)
        expected = pd.DataFrame([
            {"category_name": "Bakery", "min_price": "30.00"},
            {"category_name": "Beverages", "min_price": "20.00"},
            {"category_name": "Dairy", "min_price": "50.00"},
            {"category_name": "Frozen Foods", "min_price": "100.00"},
            {"category_name": "Fruits", "min_price": "70.00"},
            {"category_name": "Meat", "min_price": "350.00"},
            {"category_name": "Snacks", "min_price": "30.00"},
            {"category_name": "Vegetables", "min_price": "60.00"}
        ])
        # Compare both dataframes by sorting by category_name
        result_sorted = result_df.sort_values('category_name').reset_index(drop=True)
        expected_sorted = expected.sort_values('category_name').reset_index(drop=True)
        # Coerce min_price to string for comparison
        result_sorted['min_price'] = result_sorted['min_price'].apply(lambda x: f"{float(x):.2f}")
        condition = result_sorted.equals(expected_sorted)
        fail_msg = f"Expected result:\\n{expected_sorted}\\nBut got:\\n{result_sorted}" if not condition else ""
        _dynamic_test(
            self,
            condition,
            "Query returns the correct minimum price for each category.",
            fail_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

Vie tietämyksesi seuraavalle tasolle edistyneillä SQL-tekniikoilla, joita käytetään todellisessa datatyössä. Opit ryhmittelemään ja suodattamaan tietoja GROUP BY- ja HAVING-lauseilla, kirjoittamaan sisäkkäisiä alikyselyitä sekä yhdistämään tauluja INNER-, LEFT-, RIGHT- ja FULL JOIN -tyypeillä. Lisäksi luot, muutat ja muokkaat tauluja DDL-lauseilla sekä lisäät, päivität ja poistat tietueita DML-lauseilla.

Opi ryhmittelemään tietoja GROUP BY -lauseella ja tiivistämään niitä aggregaattifunktioilla, kuten COUNT, SUM ja AVG. Tutustu ryhmiteltyjen tulosten suodattamiseen HAVING-lauseella sekä SQL-lauseiden oikeaan järjestykseen kyselyssä.



Opi kirjoittamaan sisäkkäisiä alikyselyitä WHERE- ja FROM-lausekkeissa monimutkaisempien kysymysten ratkaisemiseksi. Ymmärrä, miten yhdistetään kyselyiden tuloksia useista tauluista UNION-lauseen avulla.



Opi yhdistämään useita tauluja käyttämällä INNER-, LEFT-, RIGHT- ja FULL JOIN -tyyppejä. Harjoittele liitosten kirjoittamista, joiden avulla voit hakea toisiinsa liittyvää dataa tauluista yhtenäisenä tuloksena.

Tutustu SQL:n DDL- ja DML-perusteisiin: taulujen luominen CREATE-komennolla ja rajoitteilla sekä niiden muokkaaminen ALTER-, DROP- ja TRUNCATE-komennoilla. Tietueiden lisääminen, päivittäminen ja poistaminen INSERT-, UPDATE- ja DELETE-lauseilla.

Haaste: Hae Alin Hinta Kategorian Mukaan

Haaste: Hae Alin Hinta Kategorian Mukaan

Lyhyet ohjeet

Ratkaisu