## Why Filtering and Sorting Matter in Data Analysis

When you work with real-world data, you often need to answer specific business questions, such as finding the most popular products, identifying top customers, or spotting trends in sales. **Filtering** and **sorting** are two essential techniques that help you focus on the most relevant information in large datasets.

- **Filtering** lets you select only the rows that meet certain criteria, such as products in a specific category or customers from a particular city;
- **Sorting** organizes your results, such as listing products by price from lowest to highest, or showing the newest customers first.

By using these tools, you can:
- Quickly answer business questions, like "Which products in the 'Electronics' category are the most expensive?";
- Discover patterns and trends, such as which cities have the most new customers;
- Make data-driven decisions, like targeting promotions to specific groups based on their purchase history.

These skills are fundamental for analysts because they turn raw data into clear, actionable insights that help businesses grow and improve.

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_all_columns(self):
        """Check if query selects all columns from products table."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        self.cursor.execute("SELECT * FROM products LIMIT 1;")
        expected_columns = [desc[0] for desc in self.cursor.description]
        condition = set(columns) == set(expected_columns)
        _dynamic_test(
            self,
            condition,
            "Query selects all columns from products table.",
            f"Query does not select all columns from products table. Found: {columns}"
        )

    def test_only_electronics_category(self):
        """Check if only products with category 'Electronics' are returned."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        if len(df) == 0:
            condition = False
            failure_msg = "No rows returned. Should return products in 'Electronics' category."
        else:
            non_electronics = df[df['category'] != 'Electronics']
            condition = len(non_electronics) == 0
            if not condition:
                failure_msg = f"Found products not in 'Electronics' category: {non_electronics.to_dict('records')}"
            else:
                failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "Only products in 'Electronics' category are returned.",
            failure_msg
        )

    def test_ordered_by_price_desc(self):
        """Check if results are ordered by price descending."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        if len(df) == 0:
            condition = False
            failure_msg = "No rows returned. Cannot check order."
        else:
            prices = df['price'].tolist()
            sorted_prices = sorted(prices, reverse=True)
            condition = prices == sorted_prices
            failure_msg = f"Prices are not ordered descending: {prices}"
        _dynamic_test(
            self,
            condition,
            "Results are ordered by price descending.",
            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

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: Product Table Exploration

Why Filtering and Sorting Matter in Data Analysis

解答