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 table1SELECT2 category,3 avg_price,4 product_count-- The result of the inner query is used like a table5FROM ( -- Inner query: one row per category6 SELECT7 category, -- Average price of the category, named avg_price8 AVG(price) AS avg_price, -- Number of products in the category, named product_count9 COUNT(*) AS product_count10 FROM11 products -- GROUP BY makes one group per category for AVG and COUNT12 GROUP BY13 category-- A derived table must have a name; here category_stats14) AS category_stats-- Filters on the computed count, like HAVING would15WHERE -- Keeps only categories with at least 5 products16 product_count >= 5-- Most expensive category on average first: Electronics, Furniture, Clothing17ORDER BY18 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. 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