subqueries must be aggregated before hand

Looks like Spark subqueries does not work well with variables and values from outside of subquery. For instance, this query work on PgSQL:
 

SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten) FROM tenk1 s WHERE unique2 < 10;

However, it does not work in Spark.