SQL Quest › SQL Interview Questions › Subqueries & CTEs

Subquery in FROM (Derived Table)

MediumFreeQuerying BasicsSubqueries & CTEsJoinsAggregation & GroupingConditional Logic

A derived table is a subquery in the FROM clause — it creates a temporary result you can query like a regular table. First, create a derived table that counts orders per customer. Then JOIN it with customers to show name, membership, order_count, and whether they're above_average ('Yes' if order_count > average orders per customer, 'No' otherwise). Sort by order_count descending, then name ascending for ties.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

orders

order_id	customer_id	product	category	quantity	price	total	order_date	country	status
1	1	Laptop Pro	Electronics	1	1299.99	1299.99	2024-01-15	USA	completed
2	2	Wireless Mouse	Electronics	2	49.99	99.98	2024-01-16	Canada	completed
3	3	Office Chair	Furniture	1	349.99	349.99	2024-01-17	USA	completed

customers

customer_id	name	email	signup_date	membership	total_orders
1	John Smith	john.smith@email.com	2023-01-15	Gold	15
2	Emma Wilson	emma.wilson@email.com	2023-03-20	Silver	8
3	Michael Brown	michael.brown@email.com	2023-02-10	Gold	12

Expected output: Alice | Gold | 5 | Yes

Hint

FROM (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) o JOIN customers c ON o.customer_id = c.customer_id. The subquery in parentheses IS the derived table.

Concepts

SELECT Subquery Derived Table JOIN GROUP BY CASE

Practise the topic: SQL practice questions · CTE practice · JOIN practice · GROUP BY exercises · CASE WHEN practice

In these company practice sets

Snowflake

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Subquery in FROM (Derived Table)

Schema

Hint

Concepts

In these company practice sets

Related questions