# The Scorched Earth Reviews

> Someone was unhappy. Find out how many times.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The quality assurance team is pulling all products that received the lowest possible rating (1) to investigate whether there's a common defect. Show each product's name and rating.

## Worked solution and explanation

### Why this problem exists in real interviews

The products table contains product_name and category values that must be processed with query construction. This appears as a fundamentals check to probe whether you reason about the correct aggregation grain before writing any window or GROUP BY clause.

---

### Break down the requirements

#### Step 1: Filter to rating = 1

`WHERE rating = 1` selects only the lowest-rated products.

#### Step 2: Return product name and rating

`SELECT product_name, rating` shows the requested fields.

---

### The solution

**Equality filter on rating**

```sql
SELECT product_name, rating
FROM products
WHERE rating = 1
```

> **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

- What happens to your result if products.rating contains NULLs for some rows? _(Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on rating.)_
- How would you verify that your aggregation on products.product_id is not double-counting due to duplicate rows? _(Tests data quality awareness and deduplication strategies.)_
- What index would you add to products to avoid a full table scan when filtering or sorting by product_id? _(Tests practical indexing decisions for numeric filter columns.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/one_star_product_review_count)
- [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.