# Cheapest High-Rated Product

> Cheap and highly rated. A rare combination.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

A budget-conscious customer wants the best deal on highly rated electronics. Find the single cheapest product with a rating of at least 4.0, available stock, and a category containing 'Electronics'. Show just the product name and price.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests multi-condition WHERE filtering with ORDER BY and LIMIT. It probes whether you can chain conditions precisely (rating threshold, stock availability, category substring match) and retrieve exactly one row.

---

### Break down the requirements

#### Step 1: Apply all filters

`WHERE rating >= 4.0 AND in_stock = 1 AND category LIKE '%Electronics%'` combines the three conditions.

#### Step 2: Find the cheapest

`ORDER BY price ASC LIMIT 1` returns the single cheapest qualifying product.

#### Step 3: Select required columns

Return only `product_name` and `price` as specified.

---

### The solution

**Multi-filter with top-1 selection**

```sql
SELECT product_name, price
FROM products
WHERE rating >= 4.0
  AND in_stock = 1
  AND category LIKE '%Electronics%'
ORDER BY price ASC
LIMIT 1
```

> **Cost Analysis**
>
> Scan of 8K rows with multi-condition filtering. At this scale, any approach is instant. A composite index on `(category, rating, in_stock, price)` would help if the table were millions of rows.

> **Interviewers Watch For**
>
> Whether the candidate uses `category LIKE '%Electronics%'` (substring) vs `category = 'Electronics'` (exact match). The prompt says "containing," so LIKE with wildcards is correct.

> **Common Pitfall**
>
> Using `LIMIT 1` without `ORDER BY` returns an arbitrary row, not the cheapest. The sort must come before the limit.

---

## Common follow-up questions

- What if multiple products are tied at the lowest price? _(LIMIT 1 picks one arbitrarily. Tests whether to use RANK or FETCH FIRST WITH TIES.)_
- How would you handle in_stock being a boolean vs integer? _(Tests awareness of type coercion: in_stock = true vs in_stock = 1.)_
- What if the category filter needed to be case-insensitive? _(Tests ILIKE or LOWER() for portable case-insensitive matching.)_

## Related

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