How can we consider writing a SQL query inside the materialized view?how can it be more efficient?
Query 1 ? sales and profitability by quarter by product.
select t.quarter, p.product_id, p.product_name,
sum(f.purchase_price + f.shipping_charge) as Gross_Sales,
sum((f.purchase_price + f.shipping_charge)-(p.cost_price + p.shipping_charge)) as Profit, count(purchase_price) as total_sales, count(*) as purchase_count
from time t, product p, purchases f
where t.time_key = f.time_key and f.product_id = p.product_id
group by t.quarter, p.product_id, p.product_name;
Query 2 ? sales analysis by month by supplier by product.
select p.supplier, t.month, sum(p.cost_price + p.shipping_charge) as Cost_Price, count(purchase_price) as total_sales, count(*) as Unit_Sales
from time t, product p, purchases f
where t.time_key = f.time_key and f.product_id = p.product_id
group by p.supplier, t.month
order by p.supplier, t.month ;
Query 3 ? sales analysis by quarter by customer city by product.
select c.city, t.quarter, p.product_id, p.product_name,
sum(f.purchase_price + f.shipping_charge) as Gross_Sales,
sum((f.purchase_price + f.shipping_charge)-(p.cost_price + p.shipping_charge)) as Profit, count(purchase_price) as total_sales, count(*) as purchase_count
from time t, product p, purchases f, customer c
where t.time_key = f.time_key and f.product_id = p.product_id and f.customer_id = c.customer_id
group by c.city, t.quarter, p.product_id, p.product_name
order by c.city, t.quarter, p.product_id, p.product_name;
which one query would be more efficient to make use in a materialized view?so that all thses queries use the M views