How to find Revenue for the customers

 

 

 

 

 

 

 WITH customer as (select CUSTOMER_ID, CUST_FIRST_NAME,CUST_LAST_NAME from customers ) , order1 as (select ORDER_ID , CUSTOMER_ID from orders ) , order_item as (select ORDER_ID,SUM(UNIT_PRICE*QUANTITY) as revenue_per_order from order_items GROUP BY ORDER_ID) select order1.CUSTOMER_ID , customer.CUST_FIRST_NAME , customer.CUST_LAST_NAME , SUM(order_item.revenue_per_order) as revenue_per_customer from customer left outer join order1 on customer.customer_id = order1.customer_id left outer join order_item on order1.order_id = order_item.order_id group by order1.customer_id , CUST_FIRST_NAME , CUST_LAST_NAME order by revenue_per_customer desc; old format ********** WITH customer as (select CUSTOMER_ID, CUST_FIRST_NAME,CUST_LAST_NAME from customers ) , order1 as (select ORDER_ID , CUSTOMER_ID from orders ) , order_item as (select ORDER_ID,SUM(UNIT_PRICE*QUANTITY) as revenue_per_order from order_items GROUP BY ORDER_ID) select order1.CUSTOMER_ID , customer.CUST_FIRST_NAME , customer.CUST_LAST_NAME , SUM(order_item.revenue_per_order) as revenue_per_customer from customer , order1 , order_item WHERE customer.customer_id = order1.customer_id(+) AND order1.order_id = order_item.order_id(+) group by order1.customer_id , CUST_FIRST_NAME , CUST_LAST_NAME order by revenue_per_customer desc;