Combine Order and Customer Data for Efficient Reporting

Create a Materialized View with a JOIN

Write a PostgreSQL query to create a materialized view that joins the Orders and Customers tables.

Solution:

-- Create a materialized view to display order details along with customer names.
CREATE MATERIALIZED VIEW OrderCustomerSummary AS
SELECT o.order_id, o.order_date, c.customer_name
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id;

Explanation:

• Purpose of the Query:
• The goal is to combine data from two related tables into a materialized view for easier querying.
• This demonstrates using a JOIN within a materialized view definition.
• Key Components:
• JOIN Customers c ON o.customer_id = c.customer_id : Links orders with customer details.
• Aliases (o and c) simplify the query structure.
• Real-World Application:
• Provides a unified view for sales analysis and customer relationship management.

Notes:

• As with other materialized views, the data must be refreshed periodically.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a materialized view that joins the Invoices and Customers tables to show invoice numbers, dates, and customer names.
• Write a PostgreSQL query to create a materialized view that joins the Orders and Shippers tables to display order_id, shipper_name, and shipping_date.
• Write a PostgreSQL query to create a materialized view that joins the Products and Suppliers tables to show product details along with supplier contact information.
• Write a PostgreSQL query to create a materialized view that joins the Reservations and Hotels tables to list reservation_id, hotel_name, and check-in date.

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

Previous PostgreSQL Exercise: Create a Materialized View with Aggregation.
Next PostgreSQL Exercise: Create a Materialized View with Filtering.