Derived Table: Subquery in the FROM Clause
Wrap an aggregated subquery in FROM to rank products by total revenue.
Overview
A subquery in the FROM clause is called a derived table (or inline view).
It produces a temporary result set you can join and aggregate further. Here we
first compute per-product revenue, then rank those products by revenue — a two-
stage aggregation that's clean as a derived table.
The Query
Store (Customers/Orders/Products)
Loading database engine...
Step-by-Step Breakdown
- Inner subquery computes total revenue per product.
rankedsubquery does the same but adds aRANK()window over revenue.- We select from
ranked(joined toprodmerely to demonstrate aliasing) and order by rank.
Derived tables need aliases
Every derived table must have an alias (here prod, ranked) or the query
fails to parse.
Variations
Simpler single derived table with revenue and rank:
Store (Customers/Orders/Products)
Loading database engine...
Common Mistakes
- Missing alias on the derived table.
FROM (…)withoutAS aliasis a syntax error. - Referencing outer columns. A derived table can't see columns from the
outer query (that's what correlated subqueries/
LATERALare for). - Duplicate column names. Join two derived tables with overlapping column names and you must qualify them everywhere.