Subqueries

Ask a question inside a question

Lesson 9 of 20 · 9 min · Module 3: See patterns in data

Sometimes a filter needs a value that must first be calculated from other rows. A subquery provides that value.

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Use an aggregate result as a comparison value
Separate the inner and outer questions
Keep a query responsive to changing data

The idea

A scalar subquery returns one value, such as (SELECT AVG(xp) FROM learners). The outer query can compare each row against it.

SELECT name, xp FROM learners
WHERE xp > (SELECT AVG(xp) FROM learners);

01Solve the inner question first

The inner query finds the average: 940 XP divided by five learners is 188. The outer query keeps rows whose XP is greater than that threshold.

02Avoid a hard-coded threshold

Writing xp > 188 happens to match this dataset, but it would not answer “above average” after the data changes. The subquery expresses the relationship you intend.

Your turn

Return name and xp for learners with XP strictly above the average across all learners. Sort by XP descending.

What the checks look for

Returns Lee and Sam above the 188 XP average

Try it in the editor →

Take this with you

Calculate the comparison value from the data rather than copying today’s answer into the query.