What is a view and a materialized view? | Interview Question

Q: What is a view and a materialized view?

**Target Audience:** Beginners who have written `SELECT` queries and keep seeing `CREATE VIEW` in schemas; candidates who need to explain freshness, refresh and engine differences. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the runnable examples) and PostgreSQL 16.15 (materialized views, plans, locking test), both run for this page. MySQL and SQL Server notes are cited. > **How to read this doc:** Each idea is in plain words first, then tagged `📌 Interview term:` with the precise vocabulary. Every result and error message below came from an actual run; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A café owner wants to know "revenue per region" several times a day. She could pin a **recipe card** on the wall: "add up all paid receipts, grouped by region". Anyone who wants the number follows the recipe, right now, using today's receipts. The answer is always current, but someone has to do the adding ev

The problem

Question presented to candidate: "What is a view, what is a materialized view, and how do you decide which one to use? What happens to each when the underlying table changes?"

What a strong answer should cover:

A view is a saved, named query: it stores no rows, and every query against it runs the definition again, so it is always current; the planner inlines it into the outer query
A materialized view stores the result rows on disk like a table: reads are as fast as reading a small table, but the data is a snapshot that is stale until REFRESH MATERIALIZED VIEW
Uses: views for reuse, simpler queries, a stable interface and column-level security; materialized views for expensive aggregates read far more often than the data changes (dashboards, reports)
Refresh behaviour in PostgreSQL: a plain refresh blocks readers; CONCURRENTLY (9.4+) lets reads continue but needs a unique index; there is no built-in automatic or incremental refresh
Engine differences: SQLite and MySQL have views but no materialized views; SQL Server has indexed views; simple views can be updatable, aggregate views are not

Code / implementation expected: Optional — CREATE VIEW, CREATE MATERIALIZED VIEW, REFRESH, and a demonstration that one is live and the other is a snapshot.

Schema used by the examples:

view

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have written SELECT queries and keep seeing CREATE VIEW in schemas; candidates who need to explain freshness, refresh and engine differences. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the runnable examples) and PostgreSQL 16.15 (materialized views, plans, locking test), both run for this page. MySQL and SQL Server notes are cited.

How to read this doc: Each idea is in plain words first, then tagged 📌 Interview term: with the precise vocabulary. Every result and error message below came from an actual run; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A café owner wants to know "revenue per region" several times a day.

She could pin a recipe card on the wall: "add up all paid receipts, grouped by region". Anyone who wants the number follows the recipe,

The solution is hidden so you can try first

13 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLA view is live: insert a row and the view result changes immediately

SQLSQLite has no materialized views: a summary table goes stale the same way

PostgreSQL: a materialized view is stale until REFRESH

CREATE MATERIALIZED VIEW paid_revenue_mv AS
SELECT region, COUNT(*) AS paid_orders, SUM(amount) AS revenue
FROM orders WHERE status = 'paid' GROUP BY region;

INSERT INTO orders (id, customer, region, status, amount)
VALUES (11, 'Kai', 'EU', 'paid', 500);

-- keep what the materialized view says now, before refreshing it
CREATE TEMP TABLE before_refresh AS SELECT * FROM paid_revenue_mv;

REFRESH MATERIALIZED VIEW paid_revenue_mv;

SELECT 'before refresh' AS step, region, paid_orders, revenue FROM before_refresh
UNION ALL
SELECT 'after refresh', region, paid_orders, revenue FROM paid_revenue_mv
ORDER BY step DESC, region;

PostgreSQL: the planner inlines a view into the outer query

CREATE VIEW paid_orders AS
SELECT id, customer, region, amount
FROM orders
WHERE status = 'paid';

-- the planner inlines the view: one scan of orders, both filters merged
EXPLAIN (COSTS OFF)
SELECT customer, amount
FROM paid_orders
WHERE region = 'EU';

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.