SQL CREATE MATERIALIZED VIEW
Nearly identical syntax to CREATE VIEW — one extra word changes everything about how it behaves.
What & Why
Add MATERIALIZED to CREATE VIEW, and Postgres runs the query immediately and stores the result physically, rather than just saving the query text.
See How It Works
BUSINESS QUESTION
Growth wants to create the daily event snapshot that the next lesson refreshes.
| id | user_id | event_name | created_at | properties |
|---|---|---|---|---|
| 1001 | 101 | signup | 2024-01-15 09:12:00+00 | {"source":"organic"} |
| 1002 | 101 | activated | 2024-01-16 14:25:00+00 | {"step":"workspace"} |
| 1003 | 102 | signup | 2024-02-10 11:08:00+00 | {"source":"paid"} |
| 1004 | 103 | report_viewed | 2024-03-12 16:40:00+00 | {"report":"retention"} |
EXAMPLE QUERY
-- Run this reviewed DDL outside the read-only lesson sandbox:
-- CREATE MATERIALIZED VIEW public.daily_event_summary AS
SELECT
created_at::date AS event_date,
event_name,
COUNT(*) AS events,
COUNT(DISTINCT user_id) AS users
FROM growth.events
GROUP BY event_date, event_name
ORDER BY event_date, event_name;RESULT — daily event summary snapshot
| event_date | event_name | events | users |
|---|---|---|---|
| 2024-01-15 | signup | 1 | 1 |
| 2024-01-16 | activated | 1 | 1 |
| 2024-02-10 | signup | 1 | 1 |
| 2024-03-12 | report_viewed | 1 | 1 |
These are the four daily rows stored by public.daily_event_summary.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario