/*
|| SFA_Queries.sql
||
|| Various queries to produce workload against simulated new 
||  Sales Force Administration application
||
*/

-----
-- Set up and initialize bind variables
-----
VARIABLE rgn_abbr 	VARCHAR2(4);
VARIABLE rgn_desc 	VARCHAR2(40);
VARIABLE cust_id    NUMBER;
BEGIN
	:rgn_abbr 	:= 'NE00';
	:rgn_desc 	:= 'South%';
	:cust_id    := 9090;
END;
/

-----
-- Query:   SPM_2.2.1
-- Purpose: Summarize quantity sold and revenue within U.S. States
-----
SELECT /*SPM_2.2.1*/ 
     C.cust_state_province	   
    ,SUM(sh.quantity_sold)
    ,SUM(sh.amount_sold)
  FROM 
  	 sh.sales SH
    ,sh.customers C
    ,sh.countries T
 WHERE SH.cust_id = C.cust_id 
   AND C.country_id = T.country_id
   AND T.country_iso_code IN ('GB','PL')
 GROUP BY C.cust_state_province
;

-----
-- Query:   SPM_2.2.2
-- Purpose: Show distribution of geographic areas within 
--          Sales Region and District
-----
SELECT /*SPM_2.2.2*/
      SR.abbr,
      SD.abbr,
      SZ.geo_id,
      COUNT(C.cust_id) "Count"
     FROM
        sfa.sales_regions SR,
        sfa.sales_districts SD,
        sfa.sales_zones SZ,
        sh.customers C
    WHERE SD.region_id = SR.region_id
      AND SZ.district_id = SD.district_id
      AND C.cust_state_province = SZ.geo_id
      AND SR.abbr = 'SE00'
    GROUP BY
       SR.abbr
      ,SD.abbr
      ,SZ.geo_id
;

-----
-- Query:   SPM_2.2.3
-- Purpose: Accumulate quantities and revenue within Sales Region, District,
--			and Territory. Note that this query +should+ take advantage of 
--          materialized view SFA.MV_SALES_SUMMARY for most effective retrieval
-----
SELECT /*SPM_2_2.3*/
    SR.abbr
   ,SD.abbr
   ,SZ.geo_id 
   ,C.cust_id
   ,SUM(SH.quantity_sold)
   ,AVG(SH.quantity_sold)
   ,COUNT(SH.quantity_sold)
   ,SUM(SH.amount_sold)
   ,AVG(SH.amount_sold)
   ,COUNT(SH.amount_sold)
  FROM 
    sfa.sales_regions SR
   ,sfa.sales_districts SD
   ,sfa.sales_zones SZ
   ,sh.customers C
   ,sh.sales SH
 WHERE SD.region_id = SR.region_id
   AND SZ.district_id = SD.district_id
   AND C.cust_state_province = SZ.geo_id
   AND C.cust_ID = SH.cust_id
 GROUP BY 
    SR.abbr
   ,SD.abbr
   ,SZ.geo_id
   ,C.cust_id    
 ORDER BY 
    SR.abbr
   ,SD.abbr
   ,SZ.geo_id
   ,C.cust_id    
;

-----
-- Query:   SPM_2.2.4
-- Purpose: Accumulate quantities and revenue within Sales Region, District,
--			and Territory by querying directly against SFA.MV_SALES_SUMMARY
-----
SELECT /*SPM_2_2.4*/
    rgn_abbr
   ,dst_abbr
   ,ter_abbr        
   ,cust_id
   ,tot_qty_sold
   ,avg_qty_sold
   ,cnt_qty_sold
   ,tot_amt_sold
   ,avg_amt_sold
   ,cnt_amt_sold
  FROM 
    sfa.mv_sales_summary
 WHERE dst_abbr = 'NE20'
 ORDER BY 
    rgn_abbr
   ,dst_abbr
   ,ter_abbr        
   ,cust_id
;  

-----
-- Query:   SPM_2.2.5
-- Purpose: Accumulate quantities and revenue within Sales Region and District.
--			for a selected Region. Note that this query +should+ take advantage 
--          of materialized view SFA.MV_SALES_SUMMARY for most effective retrieval
-----
SELECT /*SPM_2_2.5*/
    SR.abbr
   ,SD.abbr
   ,SUM(SH.quantity_sold)
   ,AVG(SH.quantity_sold)
   ,COUNT(SH.quantity_sold)
   ,SUM(SH.amount_sold)
   ,AVG(SH.amount_sold)
   ,COUNT(SH.amount_sold)
  FROM 
    sfa.sales_regions SR
   ,sfa.sales_districts SD
   ,sfa.sales_zones SZ
   ,sh.customers C
   ,sh.sales SH
 WHERE SD.region_id = SR.region_id
   AND SZ.district_id = SD.district_id
   AND C.cust_state_province = SZ.geo_id
   AND C.cust_ID = SH.cust_id
   AND SR.abbr = :rgn_abbr
 GROUP BY 
    SR.abbr
   ,SD.abbr
 ORDER BY 
    SR.abbr
   ,SD.abbr
;

-----
-- Query:   SPM_2.2.6
-- Purpose: Accumulate quantities and revenue within Sales Region. Note that 
--          this query +cannot+ take advantage of SFA.MV_SALES_SUMMARY for 
--          effective retrieval because of the selection criteria against 
--          Region Description
-----
SELECT /*SPM_2_2.6*/
    SR.abbr
   ,SUM(SH.quantity_sold)
   ,AVG(SH.quantity_sold)
   ,COUNT(SH.quantity_sold)
   ,SUM(SH.amount_sold)
   ,AVG(SH.amount_sold)
   ,COUNT(SH.amount_sold)
  FROM 
    sfa.sales_regions SR
   ,sfa.sales_districts SD
   ,sfa.sales_zones SZ
   ,sh.customers C
   ,sh.sales SH
 WHERE SD.region_id = SR.region_id
   AND SZ.district_id = SD.district_id
   AND C.cust_state_province = SZ.geo_id
   AND C.cust_ID = SH.cust_id
   AND C.cust_id = :cust_id
   AND SR.description LIKE :rgn_desc
 GROUP BY 
    SR.abbr
 ORDER BY 
    SR.abbr
;