# Create and Query Materialized Views
A [Materialized View](https://docs.deltastream.io/overview/core-concepts/databases#materialized_view) is a DeltaStream object that captures the result of a query and is continuously updated as new records are ingested in the query’s source object(s).
This article demonstrates how to create materialized views and run queries on them.
## Create a Materialized View
To begin, use the [create-materialized-view-as](https://docs.deltastream.io/reference/sql-syntax/query/materialized-view/create-materialized-view-as "mention") statement to create a new materialized view. Note that with this statement, you can only generate columns with [#primitive-data-types](https://docs.deltastream.io/reference/sql-syntax/data-types#primitive-data-types "mention") when you use the [select](https://docs.deltastream.io/reference/sql-syntax/query/select "mention") query.
### Example 1: Top-K query
#### Create a materialized view using window functions
In this first example, create a [#\_stream](https://docs.deltastream.io/overview/core-concepts/databases#_stream "mention") called `mv_pageviews` on the data stored in the `ds_pageviews` topic in your default data store. Use this DDL statement to create this stream:
```sql
CREATE STREAM mv_pageviews (
viewtime BIGINT,
userid VARCHAR,
pageid VARCHAR
) WITH ('topic'='pageviews', 'value.format'='JSON');
```
Now, if you wish to track how many pages each user visits every 30 seconds, you can use a query with a [#window-function](https://docs.deltastream.io/reference/sql-syntax/query/select#window-function "mention") to create a materialized view. Call this materialized view `visits_count`. DeltaStream keeps its content up to date in a real-time manner.
```sql
CREATE MATERIALIZED VIEW
visits_count
AS SELECT
window_start,
window_end,
userid,
COUNT(pageid) AS pgcnt
FROM TUMBLE(pageviews, size 30 SECONDS)
GROUP BY window_start, window_end, userid;
```
To review the details on the above two objects, in the lefthand navigation click **Databases** (  ), and drill down on the database to display your streams**.**
Drilling down from Database to display streams
#### Top-k query
When you have created the `visits_count` materialized view, you can run a top-k query against it. This query retrieves the top 3 users who have visited the most number of pages so far. It uses `GROUP BY` and the aggregate function `SUM` to calculate the total number of pages each user has visited across all time windows. The system orders results based on the total page counts and the top 3 records that are returned:
```sql
SELECT userid, SUM(pgcnt) AS total
FROM visits_count
GROUP BY userid
ORDER BY total DESC
LIMIT 3;
```
Here is a sample result for the query:
Result of the query
For more details on query capabilities on materialized views, please see [select-from-materialized-view](https://docs.deltastream.io/reference/sql-syntax/query/materialized-view/select-from-materialized-view "mention")
### Example 2. Combine two streams’ records
In this example, you use a materialized view to run different queries on `JOIN` results. Assume you have a new stream, called `users`, created using the DDL below. This stream captures some information about each user, such as the time a given user has registered and the user’s interests.
```sql
CREATE STREAM users_info (
registertime BIGINT,
userid VARCHAR,
regionid VARCHAR,
gender VARCHAR,
interests ARRAY,
contactinfo STRUCT
) WITH ('topic'='ds_users', 'value.format'='json');
```
#### Create a materialized view using `JOIN`
Now suppose you wish to enrich the information already captured in the `pageviews` stream by combining it with each user’s information from `users`. To do this, you can run an [#interval-join-stream-stream](https://docs.deltastream.io/reference/sql-syntax/query/select#interval-join-stream-stream "mention") query and store the result in a materialized view called `visits_info`.
```sql
CREATE MATERIALIZED VIEW
visits_info
AS SELECT p.userid AS uid,
p.pageid,
u.registertime,
u.interests[1] AS top_interest
FROM pageviews p JOIN users u
WITHIN 1 minute
ON u.userid = p.userid;
```
#### Run queries on the `JOIN` results
Now that the `visits_info` materialized view is created, DeltaStream continually updates its content in real-time as new records are ingested in either the `pageviews` or the `users`. You can retrieve the latest records in the materialized view by running queries directly on it.
For example, the below query finds info on pages visited by users whose top interest is gaming.
```sql
SELECT *
FROM visits_info
WHERE top_interest = 'Game';
```
The output looks like this:
Output of a sample query
As another example, you can find top interests of all the users who have visited a specific page (such as `Page_15`) so far and sort them according to the total user count per interest.
```sql
SELECT top_interest, count(uid) AS cnt
FROM visits_info
WHERE pageid = 'Page_15'
GROUP BY top_interest
ORDER BY cnt DESC;
```
The query results look like this:
Sample query results
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.deltastream.io/how-do-i.../creating-and-querying-materialized-views.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
