Idiomatic Materialize SQL

Materialize follows the SQL standard (SQL-92) implementation and strives for compatibility with the PostgreSQL dialect. However, for some use cases, Materialize provides its own idiomatic query patterns that can provide better performance.

Query	Idiomatic Materialize
`ANY()` Equi-join condition	Use `UNNEST()` or `DISTINCT UNNEST()` to expand the values and join.
First value within groups	Use `MIN/MAX ... GROUP BY` subquery.
Lag over a regularly increasing field	Use self join or a self `LEFT JOIN/LEFT OUTER JOIN` by an equality match on the regularly increasing field.
Last value within groups	Use `MIN/MAX ... GROUP BY` subquery
Lead over a regularly increasing field	Use self join or a self `LEFT JOIN/LEFT OUTER JOIN` by an equality match on the regularly increasing field.
Top-K	Use an `ORDER BY ... LIMIT` subquery with a `LATERAL JOIN` on a `DISTINCT` subquery (or, for K=1, a `SELECT DISTINCT ON ... ORDER BY ... LIMIT` query)