# Profitable Categories by Price

> The most profitable categories.

Canonical URL: <https://datadriven.io/problems/profitable_categories_by_price>

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

Show each category that has a positive average rating, along with the lowest price in that category and the average rating, ranked from cheapest up.

## Worked solution and explanation

### Why this problem exists in real interviews

This problem targets HAVING for post-aggregation filtering across the `products` table. You need to work with columns like `category`, `rating`, and `price` to satisfy the requirements.

---

### Break down the requirements

#### Step 1: Group by category

`GROUP BY category` collapses all products into one row per category.

#### Step 2: Filter to positive average rating

`HAVING AVG(rating) > 0` keeps only categories with a positive average.

#### Step 3: Return min price and avg rating

`MIN(price)` and `AVG(rating)` produce the two requested metrics.

#### Step 4: Order by min price ascending

`ORDER BY MIN(price) ASC` ranks from cheapest up.

---

### The solution

**Having filter for profitable categories by price**

```sql
SELECT
    category,
    MIN(price) AS min_price,
    AVG(rating) AS avg_rating
FROM products
GROUP BY category
HAVING AVG(rating) > 0
ORDER BY min_price ASC
```

> **Cost Analysis**
>
> With `products` (12,000 rows), this query scans a small dataset. No indexing is needed for this volume. At production scale, an index on the primary filter column would improve performance.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- If `rating` in `products` is NULL for some rows, how would your aggregation or join logic be affected? _(Probes understanding of NULL propagation through joins and aggregate functions on `products.rating`.)_
- The `price` column in `products` is heavily skewed toward a few popular values. How would data skew affect parallel execution of your query? _(Tests understanding of skew in `products.price` and its impact on distributed query performance.)_
- If the HAVING threshold in your query changed from a fixed number to a percentile, how would you restructure the query? _(Tests ability to replace static HAVING filters with dynamic subquery-based thresholds.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/profitable_categories_by_price)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.