Module 2 Lab: Aggregate Queries

You’ve mastered the art of summarizing data! You now know how to count, sum, average, group, and filter those groups.

This lab brings all the concepts from Module 2 together into a single, complex reporting query.

The Scenario

Our E-Commerce platform allows users to leave reviews and 5-star ratings on products. The Product Management team wants to identify which of our heavily-reviewed products are beloved, and which ones need improvement.

They have provided you with access to the reviews table, which contains:

id (The unique ID of the review)
product_id (The item being reviewed)
rating (A number from 1 to 5)

They need a report with the following specific requirements:

The report must show the product_id.
It must show the total number of reviews that product has received (alias this as review_count).
It must show the average 1-to-5 rating for that product (alias this as avg_rating).
Important: The product team doesn’t care about products with only 1 or 2 reviews. They consider that a sample size too small. They only want to see products in the report that have more than 2 reviews.
The final report should be sorted so that the products with the highest avg_rating are at the very top.

Building the Query

This is a multi-step aggregation query. Remember the execution order:

First, tell the database what table to look at (FROM).
Then, specify how the rows should be grouped together (GROUP BY).
Then, enforce the minimum review count rule on those groups (HAVING).
Then, define the columns and math you want to see (SELECT).
Finally, sort the final output (ORDER BY).

Take your time, write it step-by-step in the interactive terminal, and prove you have what it takes to generate critical business intelligence reports! Once complete, we will move on to the magic of connecting multiple tables in Module 3: Joins.