Correlated sub-query causing this error - LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join

Question

When writing correlated sub-queries in BigQuery using a WHERE clause, it is resulting in an Error. LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

However it works in other SQL engines, I am not understanding the error or how to rewrite the queries.

Different queries of the same type causing the same error.

SELECT
  out.*
FROM
  `august-monolith-205810.Assignment.students` AS out
WHERE
  2 >= (
  SELECT
    COUNT(*)
  FROM
    `august-monolith-205810.Assignment.students` AS t1
  WHERE
    out.age < t1.age)

reporting students who are older than at most two other students.

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(cnt)
FROM (
  SELECT *, COUNT(1) OVER(ORDER BY age RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) cnt
  FROM `august-monolith-205810.Assignment.students` 
)
WHERE cnt BETWEEN 1 AND 2

Another, less effective and thus not recommended but still an option is to use CROSS JOIN to mimic non-equality LEFT JOIN

#standardSQL
SELECT ANY_VALUE(out).*
FROM `august-monolith-205810.Assignment.students` AS out
CROSS JOIN `august-monolith-205810.Assignment.students` AS t1
WHERE out.age > t1.age 
GROUP BY out.id
HAVING COUNT(t1.age) <= 2