Subquery in FROM in SQL

Level 5218 keystrokes

A subquery in FROM (a derived table) first computes the average price and product count per category; the outer query keeps the categories with at least 5 products and sorts them by average price, highest first.

SQL code

-- Outer query: reads the columns of the derived table
1SELECT
2 category,
3 avg_price,
4 product_count
-- The result of the inner query is used like a table
5FROM (
-- Inner query: one row per category
6 SELECT
7 category,
-- Average price of the category, named avg_price
8 AVG(price) AS avg_price,
-- Number of products in the category, named product_count
9 COUNT(*) AS product_count
10 FROM
11 products
-- GROUP BY makes one group per category for AVG and COUNT
12 GROUP BY
13 category
-- A derived table must have a name; here category_stats
14) AS category_stats
-- Filters on the computed count, like HAVING would
15WHERE
-- Keeps only categories with at least 5 products
16 product_count >= 5
-- Most expensive category on average first: Electronics, Furniture, Clothing
17ORDER BY
18 avg_price DESC;

Output

+-------------+-----------+---------------+
| category    | avg_price | product_count |
+-------------+-----------+---------------+
| Electronics |    450.00 |            12 |
| Furniture   |    250.00 |             8 |
| Clothing    |     75.00 |            15 |
+-------------+-----------+---------------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 7
  • ,× 4
  • (× 3
  • )× 3
  • ;× 1
  • *× 1
  • =× 1
  • >× 1

More SQL examples at level 5

← SQL typing practice