Precompute Monthly Sales for Dynamic Dashboard Reporting

Create a Materialized View for a Reporting Dashboard

Write a PostgreSQL query to create a materialized view that summarizes monthly sales figures for a dashboard.

Solution:

-- Create a materialized view to calculate monthly sales totals.
CREATE MATERIALIZED VIEW MonthlySalesReport AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_sales
FROM Orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Explanation:

• Purpose of the Query:
• The goal is to precompute monthly sales totals for fast reporting and dashboard display.
• This demonstrates the combination of aggregation and date functions in a materialized view.
• Key Components:
• DATE_TRUNC('month', order_date) : Groups orders by month.
• SUM(amount) AS total_sales : Aggregates the sales amounts per month.
• Real-World Application:
• Ideal for business intelligence dashboards where timely sales insights are crucial.

Notes:

• Refreshing the view updates the dashboard with the latest sales data.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a materialized view that summarizes quarterly revenue for use in a financial dashboard.
• Write a PostgreSQL query to create a materialized view that aggregates user engagement metrics for a marketing dashboard, grouping by week.
• Write a PostgreSQL query to create a materialized view that displays department-wise expense totals for a management dashboard.
• Write a PostgreSQL query to create a materialized view that compiles key performance indicators (KPIs) for a sales dashboard from multiple data sources.

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

Previous PostgreSQL Exercise: Create a Materialized View with Sorting.
Next PostgreSQL Exercise: Refresh a Materialized View Concurrently.