Precompute Advanced Analytics Using Subqueries in a View

Create a Complex Materialized View with Subqueries

Write a PostgreSQL query to create a materialized view that includes data from a subquery to calculate top-selling products.

Solution:

-- Create a materialized view to display top-selling products.
CREATE MATERIALIZED VIEW TopSellingProducts AS
SELECT product_id, total_sales
FROM (
  SELECT product_id, SUM(amount) AS total_sales
  FROM Sales
  GROUP BY product_id
) AS SalesData
WHERE total_sales > 10000;

Explanation:

• Purpose of the Query:
• The goal is to precompute and store results that include a subquery for filtering high-performing products.
• This demonstrates the use of subqueries within a materialized view definition.
• Key Components:
• The subquery aggregates sales per product.
• WHERE total_sales > 10000 filters for top sellers.
• Real-World Application:
• Useful for executive reports that focus on products generating significant revenue.

Notes:

• As with other materialized views, periodic refreshes are needed to capture updated sales data.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a materialized view that uses a subquery to filter out low-performing products based on sales metrics.
• Write a PostgreSQL query to create a materialized view that incorporates a subquery to calculate cumulative revenue for each customer from the Orders table.
• Write a PostgreSQL query to create a materialized view that includes a subquery to identify the top 10 employees by performance score from the Performance table.
• Write a PostgreSQL query to create a materialized view that uses a subquery to extract the latest update timestamp for each record in the Audit table.

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

Previous PostgreSQL Exercise: Refresh a Materialized View Concurrently.
Next PostgreSQL Exercise: Drop and Recreate a Materialized View.