You can specify a scalar subquery as an operand of a scalar predicate in the WHERE, HAVING, and QUALIFY clauses of a query.
The following example specifies a scalar subquery (SELECT AVG(price)…) as an operand in its WHERE clause.
SELECT category, title, price FROM movie_titles AS t2 WHERE (SELECT AVG(price) FROM movie_titles AS t1 WHERE t1.category = t2.category)<(SELECT AVG(price) FROM movie_titles);