DROP MATERIALIZED VIEW cust_sales_aggr ;

CREATE MATERIALIZED VIEW cust_sales_aggr
  ON PREBUILT TABLE
  REFRESH FORCE
  ENABLE QUERY REWRITE
  AS
    SELECT c.cust_id,
      c.cust_last_name,
      c.cust_first_name,
      SUM(amount_sold) AS dollar_sales,
      COUNT(amount_sold) AS cnt_dollars,
      COUNT(*) AS cnt
    FROM sales s, customers c
    WHERE s.cust_id= c.cust_id
    GROUP BY c.cust_id,
      c.cust_last_name,
      c.cust_first_name; 