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_users_with_at_least_five_feature_uses(self):
        """Returns all user IDs who have five or more total feature events in feature_events."""
        with open("query.sql", "r") as file:
            query = file.read()
        queries = [q.strip() for q in query.strip().split(';') if q.strip()]

        # First query: users with at least 5 feature events
        self.cursor.execute(queries[0])
        rows = self.cursor.fetchall()
        user_ids = set(row[0] for row in rows)
        expected_user_ids = set()  # No user has 5 or more events in the provided data
        _dynamic_test(
            self,
            user_ids == expected_user_ids,
            "Correctly returns no users with at least five feature uses.",
            f"Expected no users, but got: {user_ids}"
        )

    def test_most_frequently_used_feature(self):
        """Returns the feature name with the highest total usage count from feature_events."""
        with open("query.sql", "r") as file:
            query = file.read()
        queries = [q.strip() for q in query.strip().split(';') if q.strip()]

        # Second query: most frequently used feature
        self.cursor.execute(queries[1])
        rows = self.cursor.fetchall()
        feature_names = [row[0] for row in rows]
        # Calculate the correct answer
        self.cursor.execute("""
            SELECT feature_name, COUNT(*) as cnt
            FROM feature_events
            GROUP BY feature_name
            ORDER BY cnt DESC
            LIMIT 1
        """)
        expected = self.cursor.fetchone()[0]
        _dynamic_test(
            self,
            len(feature_names) == 1 and feature_names[0] == expected,
            f"Correctly returns the most frequently used feature: {expected}.",
            f"Expected most used feature '{expected}', but got: {feature_names}"
        )

    def test_handles_no_users_with_five_events(self):
        """Correctly handles cases where no user meets the five-event threshold."""
        with open("query.sql", "r") as file:
            query = file.read()
        queries = [q.strip() for q in query.strip().split(';') if q.strip()]
        # First query: users with at least 5 feature events
        self.cursor.execute(queries[0])
        rows = self.cursor.fetchall()
        _dynamic_test(
            self,
            len(rows) == 0,
            "Correctly returns empty result when no user meets the five-event threshold.",
            "Should return no users, but returned: {}".format(rows)
        )

    def test_returns_only_top_feature(self):
        """Correctly returns only the top feature by usage, even if multiple features are present."""
        with open("query.sql", "r") as file:
            query = file.read()
        queries = [q.strip() for q in query.strip().split(';') if q.strip()]
        self.cursor.execute(queries[1])
        rows = self.cursor.fetchall()
        _dynamic_test(
            self,
            len(rows) == 1,
            "Returns only one feature as the most used feature.",
            f"Should only return one feature, but got: {rows}"
        )

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 SQL from the ground up with a focus on real-world product analytics. Learn to analyze sessionized data, measure retention and churn, track feature adoption, and perform A/B test analysis using advanced SQL techniques. This course is designed for aspiring product analysts with no prior SQL experience, blending engaging theory with hands-on challenges.

Learn how to structure, query, and analyze sessionized user data, a foundational skill for product analytics.

Dive into retention and churn metrics, learning how to measure and interpret user engagement over time.

Learn to track and analyze how users discover and adopt new product features using SQL.

Master advanced SQL techniques for analyzing A/B tests and using window functions for product metrics.

Challenge: Repeat Usage Analysis

解答