Every question here needs a result from one query to answer another. Choose the shape that reads best:
- a subquery for one value or a list (
> (SELECT AVG(...)), IN (SELECT ...));
- a correlated subquery to compare each row with its own group;
- EXISTS or NOT EXISTS for "has any" and "has none";
- a CTE when the inner step is long or used twice.
Several shapes give the same rows; the check accepts any of them. When a list could contain NULLs, prefer NOT EXISTS to NOT IN.