Above 100 Product Stats per Category

The problem

Brightlane's merchandising team is analyzing price distribution across product categories.

Write a query to return every category ID, the total number of products in that category, the count of products with price greater than $100, and the average price among products with price greater than $100.

Assumptions:

The products table has one row per product with a category_id and a price.
Each category_id with at least one product should appear once.
For each category, the total count covers every product. The above-$100 count and the above-$100 average cover only products whose price is strictly greater than $100.

Output:

One row per category, with columns category_id, total_products, above_100_count, and avg_above_100_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 total_products,
  COUNT(*) FILTER (
    WHERE
      price > 100
  ) AS above_100_count,
  AVG(price) FILTER (
    WHERE
      price > 100
  ) AS avg_above_100_price
FROM
  products
GROUP BY
  category_id

The shape

Two aggregates share the same FILTER (WHERE price > 100): one counts the above-$100 products and the other averages their prices. Both are restricted to the same subset of each category_id partition, while a third aggregate — COUNT(*) — runs unfiltered across the whole partition.

Clause by clause

SELECT category_id, COUNT(*) AS total_products, COUNT(*) FILTER (WHERE price > 100) AS above_100_count, AVG(price) FILTER (WHERE price > 100) AS avg_above_100_price returns each category's full product count, the count of products above $100, and the average price among that above-$100 subset. The two FILTER clauses target the same condition but feed different aggregates, so COUNT and AVG describe the same restricted set with different statistics.
FROM products reads the product records.
GROUP BY category_id partitions the rows per category, including the partition where category_id is NULL — GROUP BY treats every NULL as a single group, so the three products with no category_id collapse into one row.

The trap

AVG(price) FILTER (WHERE price > 100) is not the same as AVG(price) > 100. The first computes the average across only those products whose price exceeds $100, ignoring the cheaper ones entirely. A category like category 8 with five products and one above $100 (priced at $129.99) shows an above-$100 average of 129.99 — that single product's price, not a category-wide average. For a category with zero above-$100 products, both the FILTER-ed AVG and SUM return NULL because the aggregate sees an empty input. COUNT(*) FILTER returns 0 in the same situation; counts and sums diverge on empty inputs.

You practiced two parallel FILTER aggregates sharing the same condition — COUNT and AVG both restricted to the same subset, computing different statistics within that subset.

Return every category ID, the total number of products in that category, the count of products with `price` greater than `$100`, and the average `price` among products with `price` greater than `$100`

The shape

Clause by clause

The trap

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