Product Summary by Category — CREATE TABLE AS in SQL

The problem

Brightlane's product analytics pipeline caches per-category metrics in a temp table to support multiple catalog reports in the same session. The query that populates the temp table needs to return the product count and average price for each category_id.

Write a query to return the category ID, product count, and average price for each category_id value.

Assumptions:

The products table has one row per product with a category_id and a price.
Each category_id value present in products should appear once in the result.
For each category, the product count is the number of products in that category_id. The average price is the average of price across those products.

Output:

One row per category, with columns category_id, product_count, and avg_price.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  category_id,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price
FROM
  products
GROUP BY
  category_id

The shape

A per-category aggregation: group the products table by category_id, then count the rows and average the prices inside each group. The result is one row per category with two metrics already computed, which is the shape worth caching in a temp table when multiple reports need the same numbers.

Clause by clause

SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price returns three columns. category_id labels each group; COUNT(*) counts the products inside that group; AVG(price) averages the prices in that group. So category 8 reports five products at an average price of 72.99.
FROM products reads every product in the catalog. No filter is applied; every row contributes to some group.
GROUP BY category_id partitions the rows by category. The aggregates run once per partition rather than once across the entire table. The category_id = NULL rows form their own group, because GROUP BY treats NULL as a value for grouping purposes even though it is not equal to itself.

The trap

AVG is not the same as SUM / COUNT once NULL enters the picture. AVG(price) skips rows where price is NULL entirely. If half of a category's products had a missing price, the average would be over the populated half, not divided by the full product count. On this data every price is recorded, so the two formulas agree. The moment a NULL price exists, AVG(price) and SUM(price) / COUNT(*) diverge.

You practiced a per-category aggregation as the body of a CTAS — the materialized result becomes a queryable table with three columns inferred directly from the SELECT list.

Return the category ID, product count, and average price for each `category_id` value

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.