Above Average
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- 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.
Products beating the catalog average.
Tables
products
Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER
| 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
| 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.
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
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 DESCCommon 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.)