# Idiomatic Materialize SQL
Learn about idiomatic Materialize SQL. Materialize offers various idiomatic query patterns, such as for top-k query pattern, first value/last value query paterrns, etc.
> For the complete documentation index, see [llms.txt](https://preview.materialize.com/materialize/40269/llms.txt).
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.
## Window functions
| Window Function | 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) |
## General query patterns
| Query Pattern | Idiomatic Materialize |
| --- | --- |
| ANY() Equi-join condition | Use UNNEST() or DISTINCT UNNEST() to expand the values and join. |
| NOT IN (<subquery>) predicate | Rewrite to NOT EXISTS, or filter out NULLs on both sides of the NOT IN. |
| mz_now() with date/time operators | Move the operation to the other side of the comparison: |
| mz_now() with disjunctions (OR) in materialized/indexed view definitions and SUBSCRIBE statements: | Rewrite using UNION ALL or UNION (deduplicating as necessary) expression |
## Pages in this section
- [`ANY()` equi-join condition](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/any/index.md): Use idiomatic Materialize SQL for equi-join whose `ON` expression includes the `ANY()` operator.
- [`NOT IN` subquery](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/not-in/index.md): Use idiomatic Materialize SQL for `NOT IN (subquery)` predicates to avoid a cross join in the dataflow plan.
- [Appendix](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/appendix/index.md): Idiomatic Materialize appendix, containing example data and cheatsheets
- [First value in group](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/first-value/index.md): Use idiomatic Materialize SQL to find the first value in each group.
- [Lag over](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/lag/index.md): Use idiomatic Materialize SQL to access the previous row's value (lag) when ordered by a field that advances in a regular pattern, such as in regular intervals.
- [Last value in group](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/last-value/index.md): Use idiomatic Materialize SQL to find the last value in each group.
- [Lead over](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/lead/index.md): Use idiomatic Materialize SQL to access the next row's value (lead) when ordered by a field that advances in a regular pattern, such as in regular intervals.
- [mz_now() expressions](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/mz_now/index.md): `mz_now()` expressions can only take comparison operators. `mz_now()` expressions cannot be used with disjunctions `OR` in view definitions.
- [Top-K in group](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/top-k/index.md): Use idiomatic Materialize SQL to find the top-k/top-n elements in each group.