SQL Quest › SQL Interview Questions › Subqueries & CTEs
Filter on an Average You Just Computed
Sales want the countries where the average order is worth more than 200. The average has to be computed first and only then filtered.
Show exactly these 2 columns, in this order: country, avg_order. avg_order is the average of total across that country's orders, rounded to 2 decimals and aliased exactly avg_order. Keep only countries whose avg_order is strictly greater than 200. Order by avg_order descending, then country ascending.
The new idea: a whole query can sit in the FROM clause. FROM (SELECT country, ROUND(AVG(total), 2) AS avg_order FROM orders GROUP BY country) AS country_avg builds a small table of one row per country, and the outer query then treats it like any other table — so WHERE avg_order > 200 is legal, filtering a column that did not exist a moment earlier.
Two things people forget. The derived table needs a name (AS country_avg), or SQLite has nothing to call the thing you just built. And yes, HAVING ROUND(AVG(total), 2) > 200 would also work here — the derived table earns its keep once that computed column is used more than once or joined to something, and this is the smallest place to see the shape.
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 |
Expected output: 3 rows — Germany 302.49, USA 256.42, Japan 213.32
Hint
SELECT, Subquery, Derived Table, and open the hint there if you stall.Concepts
SELECT Subquery Derived Table Aggregation GROUP BY
Practise the topic: SQL practice questions · CTE practice · GROUP BY exercises
In these company practice sets
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test