Store Aggregated Sales Data in a Materialized View

Create a Materialized View

Write a PostgreSQL query to create a materialized view that stores aggregated sales data from the Orders table.

Solution:

-- Create a materialized view to store total sales per customer.
CREATE MATERIALIZED VIEW CustomerSales AS
SELECT customer_id, SUM(amount) AS total_sales
FROM Orders
GROUP BY customer_id;

Explanation:

• Purpose of the Query:
• The goal is to precompute and store complex aggregation results to improve query performance.
• This demonstrates how to create a materialized view that physically stores data.
• Key Components:
• CREATE MATERIALIZED VIEW CustomerSales AS : Specifies the creation of a materialized view.
• SUM(amount) AS total_sales aggregates sales for each customer.
• Real-World Application:
• Useful for dashboards and reporting where data is read frequently but updated less often.

Notes:

• Materialized views require manual refreshing to reflect changes in the underlying tables.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a materialized view that precomputes daily visitor counts from a web traffic log.
• Write a PostgreSQL query to create a materialized view that stores summarized financial data grouped by fiscal quarter.
• Write a PostgreSQL query to create a materialized view that calculates the average order value per customer from the Orders table.
• Write a PostgreSQL query to create a materialized view that aggregates product review scores by product, filtering out products with fewer than 10 reviews.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Create a View with Computed Columns.
Next PostgreSQL Exercise: Refresh a Materialized View.