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)