Documentation
Docs
Pricing
Blog
About
Sign In
Get Started
Docs
Pricing
Blog
About
Sign In
Get Started
Get started
What is Materialize?
Quickstart
Download and run Materialize Emulator
Concepts
Clusters
Sources
Views
Indexes
Sinks
Ingest data
MySQL
Amazon Aurora
Amazon RDS
Azure DB
Google Cloud SQL
Self-hosted
PostgreSQL
AlloyDB
Amazon Aurora
Amazon RDS
Azure DB
Google Cloud SQL
Neon
Self-hosted
SQL Server
Using Kafka and Debezium
Kafka
Amazon MSK
Confluent Cloud
Self-hosted Kafka
Upstash Kafka
WarpStream
Redpanda
Self-hosted Redpanda
Redpanda Cloud
Webhooks
Quickstart
Amazon EventBridge
HubSpot
RudderStack
Segment
SnowcatCloud
Stripe
Network security
Static IP addresses
AWS PrivateLink connections
SSH tunnel connections
Troubleshooting
Transform data
Overview
Idiomatic Materialize SQL
First value in group
Lag over
Last value in group
Lead over
Top-K in group
Appendix
Idiomatic Materialize SQL chart
Window function to idiomatic Materialize
Example data: items and orders
Patterns
Durable subscriptions
Percentile calculation
Rules execution engine
Temporal filters (time windows)
Query optimization
Troubleshooting
Serve results
Query using
SELECT
Query using external tools
Deepnote
Hex
Metabase
Power BI
Tableau
Looker
Subscribe to results (
SUBSCRIBE
)
Sink results to external tools
Amazon S3
Census
Kafka
Redpanda
Snowflake
Troubleshooting sinks
Manage Materialize
Monitoring
Alerting
Datadog
Grafana
Use dbt to manage Materialize
Development workflows
Use Terraform to manage Materialize
Access control
Role-based access control (RBAC)
Manage privileges
Manage roles
Tutorial: Manage privileges in a non-production cluster
Tutorial: Manage privileges with Terraform
Usage & billing
Reference
Sources
Redpanda
Kafka
MySQL
PostgreSQL
Load generator
Webhook
Sinks
Kafka
Redpanda
SQL commands
ALTER … OWNER
ALTER … RENAME
ALTER … SWAP
ALTER CLUSTER
ALTER CONNECTION
ALTER DEFAULT PRIVILEGES
ALTER INDEX
ALTER MATERIALIZED VIEW
ALTER ROLE
ALTER SECRET
ALTER SINK
ALTER SOURCE
ALTER SYSTEM RESET
ALTER SYSTEM SET
ALTER TABLE
BEGIN
CLOSE
COMMENT ON
COMMIT
COPY FROM
COPY TO
CREATE CLUSTER
CREATE CLUSTER REPLICA
CREATE CONNECTION
CREATE DATABASE
CREATE INDEX
CREATE MATERIALIZED VIEW
CREATE ROLE
CREATE SCHEMA
CREATE SECRET
CREATE SINK
CREATE SOURCE
CREATE TABLE
CREATE TYPE
CREATE VIEW
DEALLOCATE
DECLARE
DELETE
DISCARD
DROP CLUSTER
DROP CLUSTER REPLICA
DROP CONNECTION
DROP DATABASE
DROP INDEX
DROP MATERIALIZED VIEW
DROP OWNED
DROP ROLE
DROP SCHEMA
DROP SECRET
DROP SINK
DROP SOURCE
DROP TABLE
DROP TYPE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN FILTER PUSHDOWN
EXPLAIN PLAN
EXPLAIN SCHEMA
EXPLAIN TIMESTAMP
FETCH
GRANT PRIVILEGE
GRANT ROLE
INSERT
PREPARE
REASSIGN OWNED
RESET
REVOKE PRIVILEGE
REVOKE ROLE
ROLLBACK
SELECT
Joins
Recursive CTEs
SET
SHOW
SHOW CLUSTER REPLICAS
SHOW CLUSTERS
SHOW COLUMNS
SHOW CONNECTIONS
SHOW CREATE CLUSTER
SHOW CREATE CONNECTION
SHOW CREATE INDEX
SHOW CREATE MATERIALIZED VIEW
SHOW CREATE SINK
SHOW CREATE SOURCE
SHOW CREATE TABLE
SHOW CREATE VIEW
SHOW DATABASES
SHOW DEFAULT PRIVILEGES
SHOW INDEXES
SHOW MATERIALIZED VIEWS
SHOW OBJECTS
SHOW PRIVILEGES
SHOW ROLE MEMBERSHIP
SHOW ROLES
SHOW SCHEMAS
SHOW SECRETS
SHOW SINKS
SHOW SOURCES
SHOW SUBSOURCES
SHOW TABLES
SHOW TYPES
SHOW VIEWS
SUBSCRIBE
TABLE
UPDATE
VALIDATE CONNECTION
VALUES
SQL functions & operators
Aggregate function filters
array_agg function
CAST function and operator
COALESCE function
csv_extract function
date_bin function
date_part function
date_trunc function
datediff function
encode and decode functions
EXTRACT function
jsonb_agg function
jsonb_object_agg function
justify_days function
justify_hours function
justify_interval function
LENGTH function
list_agg function
map_agg function
now and mz_now functions
Pushdown functions
string_agg function
SUBSTRING function
TIMEZONE and AT TIME ZONE functions
to_char function
SQL data types
Array types
boolean type
bytea type
date type
Floating-point types
Integer types
interval type
jsonb type
List types
map type
mz_aclitem type
mz_timestamp type
numeric type
oid type
record type
text type
time type
Timestamp types
Unsigned Integer types
uuid type
SQL identifiers
SQL namespaces
System catalog
mz_catalog
pg_catalog
information_schema
mz_internal
mz_introspection
System clusters
Tools and integrations
Overview
CLI
Installation
Configuration
Command reference
mz app-password
mz config
mz profile
mz region
mz secret
mz sql
mz user
SQL clients
SQL drivers
Go
Java
Node.js
PHP
Python
Ruby
Rust
HTTP API
WebSocket API
Releases and previews
Releases
Preview features
About
Customer responsibility model
Support
License
Free trial FAQs
Security overview
Responsible disclosure policy
Close
Arrow Pointing to the left
All Topics
Home
/
Overview
/
Idiomatic Materialize SQL
Appendix
Example data: items and orders
Idiomatic Materialize SQL chart
Window function to idiomatic Materialize
Back to top ↑