# Above Average

> Products beating the catalog average.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Tata Consultancy Services

## Problem

The pricing team is identifying premium products that stand out from the rest of the catalog. Show the name, category, and price of every product whose price exceeds the overall catalog average, and include a column with that catalog-wide average for reference. Present results from most expensive to least.

## Tables

### products

- product_id INTEGER
- product_name TEXT
- category TEXT
- price REAL
- rating REAL
- in_stock INTEGER

Sample rows (10 of 200):

| product_id | product_name | category | price | rating | in_stock |
| --- | --- | --- | --- | --- | --- |
| 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 |
| 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 |
| 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 |
| 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 |
| 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 |
| 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 |
| 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 |
| 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 |
| 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 |
| 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 |

## Expected output

First 5 of 96 rows:

| product_name | category | price | catalog_avg |
| --- | --- | --- | --- |
| Basic Set v100 | Electronics | 1859.72 | 896.6304188481676 |
| Premium Widget v99 | Music | 1850.45 | 896.6304188481676 |
| Turbo Set v98 | Garden | 1841.18 | 896.6304188481676 |
| Eco Widget v97 | Automotive | 1831.91 | 896.6304188481676 |
| Smart Set v96 | Beauty | 1822.64 | 896.6304188481676 |

## Worked solution and explanation

### Why this problem exists in real interviews

This probes **scalar subquery filtering**, combined with aggregation. Getting it right quickly signals comfort with the building blocks that complex queries depend on.

> **Trick to Solving**
>
> The phrase "above average" or "exceeds the overall" signals a **scalar subquery**. The average must be computed across all qualifying rows, then each row compared against that single value.
> 
> 1. Write the subquery to compute the benchmark first
> 2. Use it in the WHERE clause of the outer query
> 3. Verify the subquery is uncorrelated (runs once, not per row)

---

### Break down the requirements

#### Step 1: Compute the benchmark average of `price`

Compute the catalog-wide average price with a scalar subquery (SELECT AVG(price) FROM products).

#### Step 2: Filter rows exceeding the benchmark

Filter products to those whose price exceeds that average via a correlated-free scalar subquery in WHERE.

#### Step 3: Order by the comparison column

Project `product_name`, category, price, and the same average as a reference column `catalog_avg`.

---

### The solution

**Scalar subquery with average reference column**

```sql
SELECT product_name, category, price, (SELECT AVG(price) FROM products) AS catalog_avg FROM products WHERE price > (SELECT AVG(price) FROM products) ORDER BY price DESC
```

> **Cost Analysis**
>
> The main table has 6K rows. The GROUP BY reduces the row count early, keeping downstream operations cheap.

> **Interviewers Watch For**
>
> Strong candidates state the correct `GROUP BY` grain before writing any SQL, showing they think about the output shape first. Candidates who explain correlated vs uncorrelated subqueries earn extra credibility.

> **Common Pitfall**
>
> Selecting a non-aggregated column without including it in `GROUP BY` is the most common error. Some engines reject it; others silently return arbitrary values.

---

## Common follow-up questions

- What happens if the table is empty? _(Tests awareness of edge cases: COUNT returns 0, but AVG/MIN/MAX return NULL on empty input.)_
- How would you verify the output is correct with a quick spot check? _(Tests whether the candidate can validate results against known data or row counts.)_
- What index would speed this query up the most? _(Tests basic indexing intuition: filter and join columns are the top candidates.)_
- How would the results change if you used COUNT(DISTINCT col) instead of COUNT(*)? _(Tests understanding of distinct vs total counting and when deduplication matters.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/above_average)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.