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
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)