------------------------------------------------------------
-- RATIO_TO_REPORT
------------------------------------------------------------
SET linesize 150;
column t_id format a15;

SELECT	 t_id,
	 SUM(s_amnt_sold) AS SALES,
	 AVG(SUM(s_amnt_sold)) OVER (ORDER BY t_id 
	 	 RANGE BETWEEN INTERVAL '1' DAY PRECEDING
		       AND INTERVAL '1' DAY FOLLOWING)
FROM   	 bi.spctmn
WHERE	 t_cal_week_num = 51
AND	 t_cal_year = 1999
GROUP BY t_id
ORDER BY t_id;

quit;
------------------------------------------------------------
-- CUBE example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   m_desc, t_cal_month_desc, n_iso_code, 
	 SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10')
AND      n_iso_code IN ('GB', 'US')
GROUP BY CUBE(m_desc, t_cal_month_desc, n_iso_code);

quit;
------------------------------------------------------------
-- CUME_DIST
------------------------------------------------------------
SET linesize 150;
column c_id format a15;
column p_id format a15;

SELECT	 t_cal_month AS month,
         m_desc,
	 SUM(s_amnt_sold),
	 CUME_DIST() OVER (PARTITION BY t_cal_month_desc
	                   ORDER BY SUM(s_amnt_sold) AS cume_dist_by_media
FROM   	 bi.spctmn
WHERE	 t_cal_month_desc IN ('2000-09', '2000-07', '2000-08')
GROUP BY t_cal_month_desc, m_desc;

quit;
------------------------------------------------------------
-- Densification
------------------------------------------------------------
SELECT	 p_name,
         t.year,
	 t.week,
	 NVL(Sales,0) AS DENSE_SALES
FROM	 ( SELECT   p_name, t_cal_year AS YEAR, t_cal_week_num AS WEEK,
	 	    SUM(s_amnt_sold) AS SALES
	   FROM     bi.spctmn
	   WHERE    p_name LIKE '%MOUSe%'
	   GROUP BY p_name, t_cal_year, t_cal_week_num ) v
	 PARTITION BY (v.p_name)
	 RIGHT OUTER JOIN   
	 ( SELECT DISTINCT t_cal_week_num AS WEEK, t_cal_year AS YEAR
	   FROM    bi.spctmn
	   WHERE   t_cal_year IN (2000, 2001)
	   AND	   t_cal_week_num BETWEEN 24 AND 26 ) t
	 ON (v.week = t.week AND v.year = t.year)
ORDER BY p_name, year, week;

quit;
------------------------------------------------------------
-- GROUPING ID example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   
	CASE WHEN grouping_id(m_desc)=1 THEN '*' ELSE m_desc END,
	CASE WHEN grouping_id(n_iso_code)=1 THEN '*' ELSE n_iso_code END,
	SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc = '2000-09'
AND      n_iso_code IN ('GB', 'US')
GROUP BY CUBE(m_desc, n_iso_code);

quit;


------------------------------------------------------------
-- GROUPING SETS example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   m_desc, t_cal_month_desc, n_iso_code, 
	 SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10')
AND      n_iso_code IN ('GB', 'US')
GROUP BY GROUPING SETS((m_desc, t_cal_month_desc, n_iso_code),
                       (m_desc, n_iso_code),
		       (t_cal_month_desc, n_iso_code));

quit;

------------------------------------------------------------
-- LAG and LEAD
------------------------------------------------------------
SET linesize 150;
column t_id format a15;

SELECT	 s_t_id,
	 SUM(s_amnt_sold) AS SALES,
	 LAG(SUM(s_amnt_sold),1) OVER (ORDER BY s_t_id),
	 LEAD(SUM(s_amnt_sold),1) OVER (ORDER BY s_t_id)
FROM   	 bi.spctmn
WHERE	 s_t_id >= TO_DATE('10-OCT-2000')
AND	 s_t_id <= TO_DATE('14-OCT-2000')
GROUP BY s_t_id;

quit;
------------------------------------------------------------
-- NTILE
------------------------------------------------------------
SET linesize 150;
column c_id format a15;
column p_id format a15;

SELECT	 t_cal_month_desc AS month,
	 SUM(s_amnt_sold),
	 NTILE(4) OVER (ORDER BY SUM(s_amnt_sold)) AS Tile4
FROM   	 bi.spctmn
WHERE	 t_cal_year = 2000
AND    	 p_cat = 'Electronics'
GROUP BY t_cal_month_desc;

quit;
-- This is an example file.  A semicolon terminates a statement.
-- desc x describes the schema of table x.
-- Comment lines start with two -'s

select count(*) from bi.s;
desc bi.s;
quit;
------------------------------------------------------------
-- RANK Example 1
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT	m_desc, t_cal_month_desc,
	SUM(s_amnt_sold),
	SUM(s_quantity_sold),
	RANK() OVER (ORDER BY SUM(s_amnt_sold) DESC, SUM(s_amnt_sold) DESC)
	  AS Rank
FROM   	bi.spctmn
WHERE	m_desc IN ('Direct Sales', 'Internet'),
AND     t_cal_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
GROUP BY m_desc, t_cal_month_desc;

quit;


------------------------------------------------------------
-- RANK example 2
------------------------------------------------------------
SET linesize 150;
column m_desc format a15;
column t_cal_month_desc format a15;

SELECT	*
FROM 	(
	SELECT 	m_desc,
		SUM(s_amnt_sold),
		RANK() OVER (ORDER BY SUM(s_amnt_sold)) worst,
		RANK() OVER (ORDER BY SUM(s_amnt_sold) DESC) best
	FROM   	bi.spctmn
	GROUP BY m_desc
	)
WHERE	worst < 3 OR best < 3;

quit;

------------------------------------------------------------
-- RANK example 3
------------------------------------------------------------
SET linesize 150;
column m_desc format a15;
column t_cal_month_desc format a15;
column n_iso_code format a15;

SELECT	m_desc,
	n_iso_code,
	SUM(s_amnt_sold),
	RANK() OVER (PARTITION by GROUPING_ID(m_desc, n_iso_code)
		     ORDER BY SUM(s_amnt_sold) DESC) AS rank_per_group
FROM   	bi.spctmn
WHERE	t_cal_month_desc = '2000-09'
	AND n_iso_code IN ('DK', 'US', 'JP')
GROUP BY CUBE (m_desc, n_iso_code)
HAVING	 GROUPING_ID(m_desc, n_iso_code) <> 3
ORDER BY GROUPING_ID(m_desc, n_iso_code);

quit;


-- RESULT
-- M_DESC	N_	SUM(S_AMNT_SOLD) 	RANK_PER_GROUP
-- ==============================================================
-- Direct Sales	US	x			1
-- Partners	US	x			2
-- Internet	US	x			3
-- Direct Sales	JP	x			4
-- Partners	JP	x			5
-- Internet	JP	x			6
-- Direct Sales	DK	x			7
-- Partners	DK	x			8
-- Internet	DK	x			9
-- Direct Sales		x			1
-- Partners		x			2
-- Internet		x			3
-- 		US	x			1
-- 		JP	x			2
-- 		DK	x			3
------------------------------------------------------------
-- RANK example 4
------------------------------------------------------------
SET linesize 150;
-- column c_id format a15;
-- column p_id format a15;

SELECT	c_id,
	p_id,
	RANK() OVER (ORDER BY p_id) AS r1,
	RANK() OVER (ORDER BY c_id) AS r2,
	RANK() OVER (ORDER BY 1) AS r3,
	RANK() OVER (PARTITION BY c_id ORDER BY p_id) AS r4,
	RANK() OVER (PARTITION BY p_id ORDER BY c_id) AS r5
FROM   	bi.spctmn
WHERE	c_id IN (214, 608, 699) AND p_id IN (42, 98, 123)
GROUP BY c_id, p_id;

quit;
------------------------------------------------------------
-- Multiple RANK Functions example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT	m_desc, t_cal_month_desc,
	SUM(s_amnt_sold),

	RANK() OVER (PARTITION BY t_cal_month_desc
	             ORDER BY SUM(s_amnt_sold)) DESC AS rank_within_month,
	RANK() OVER (PARTITION BY m_desc ORDER BY SUM(s_amnt_sold)) DESC
	AS rank_within_media
FROM   	bi.spctmn
WHERE	m_desc IN ('Direct Sales', 'Internet'),
AND     t_cal_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
GROUP BY m_desc, t_cal_month_desc;

quit;


------------------------------------------------------------
-- RANK with PARTITIONING example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT	m_desc, t_cal_month_desc,
	SUM(s_amnt_sold),
	RANK() OVER (PARTITION BY m_desc ORDER BY SUM(s_amnt_sold)) DESC
	AS rank_by_media
FROM   	bi.spctmn
WHERE	m_desc IN ('Direct Sales', 'Internet'),
AND     t_cal_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
GROUP BY m_desc, t_cal_month_desc;

quit;


------------------------------------------------------------
-- RANK example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT	m_desc,
	SUM(s_amnt_sold),
	RANK() OVER (ORDER BY SUM(s_amnt_sold)) AS rank
FROM   	bi.spctmn
WHERE	m_desc IN ('Direct Sales', 'Internet'),
AND     t_cal_month_desc IN ('2000-09', '2000-10')
AND     n_iso_code = 'US'
GROUP BY m_desc;

quit;


------------------------------------------------------------
-- RATIO_TO_REPORT
------------------------------------------------------------
SET linesize 150;
column c_id format a15;
column p_id format a15;

SELECT	 m_desc,
	 SUM(s_amnt_sold) AS SALES,
	 SUM(SUM(s_amnt_sold)) OVER () AS TOTAL_SALES
	 RATIO_TO_REPORT(SUM(s_amnt_sold)) OVER () AS RATIO
FROM   	 bi.spctmn
WHERE	 s_t_id = TO_DATE('11-OCT-2000')
GROUP BY m_desc;

quit;
------------------------------------------------------------
-- Reporting example 1
------------------------------------------------------------
set linesize 150;
column m_desc format a15;
column t_cal_month_desc format a17;

SELECT *
FROM   (SELECT m_desc, SUM(s_amnt_sold) mtot,
               SUM(SUM(s_amnt_sold)) OVER () gtot
        FROM   bi.spctmn
        GROUP BY m_desc)
WHERE  mtot/gtot > 1/3;


SELECT *
FROM   (SELECT m_desc, SUM(s_amnt_sold) mtot,
               SUM(SUM(s_amnt_sold)) OVER () gtot,
               RATIO_TO_REPORT(SUM(s_amnt_sold)) OVER () ratio
        FROM   bi.spctmn
        GROUP BY m_desc)
WHERE  ratio > 0.05;


-- M_DESC                MTOT       GTOT
-- --------------- ---------- ----------
-- Direct Sales    57875260.6 98205831.2


-- M_DESC                MTOT       GTOT      RATIO
-- --------------- ---------- ---------- ----------
-- Direct Sales    57875260.6 98205831.2 .589326111
-- Internet          13706802 98205831.2 .139572181
-- Partners        26346342.3 98205831.2 .268276761


-- -- The following does not work since reporting functions (nested
-- -- aggregations) are evaluated last, and con only be used in the
-- -- select clause.

-- SELECT m_desc, SUM(s_amnt_sold) mtot,
--                SUM(SUM(s_amnt_sold)) OVER () gtot
-- FROM   bi.spctmn
-- GROUP BY m_desc
-- HAVING SUM(s_amnt_sold) / 3 > SUM(SUM(s_amnt_sold)) OVER ();


quit;
------------------------------------------------------------
-- Reporting example 2
------------------------------------------------------------
set linesize 150;
column m_desc format a15;
column t_cal_month_desc format a17;

SELECT   c_id, t_cal_month_desc, SUM(s_amnt_sold),
         AVG(SUM(s_amnt_sold)) OVER (ORDER BY t_cal_month_desc
                                     ROWS 2 PRECEDING) AS MOVING_3_MONTH_AVG
FROM     bi.spctmn
WHERE    t_cal_year=1999 
AND      c_id IN (6510,6230)
GROUP BY c_id, t_cal_month_desc
ORDER BY c_id, t_cal_month_desc; 

quit;

--       C_ID T_CAL_MO SUM(S_AMNT_SOLD) MOVING_3_MONTH_AVG
-- ---------- -------- ---------------- ------------------
--       6510 1999-04         124.69             124.69
--       6510 1999-05        3395.39            1760.04
--       6510 1999-06        4080.31         2533.46333
--       6510 1999-07        6434.63         4636.77667
--       6510 1999-08        5104.73         5206.55667
--       6510 1999-09        4676.23         5405.19667
--       6510 1999-10        5108.68         4963.21333
--       6510 1999-11         801.64            3528.85

------------------------------------------------------------
-- Reporting example 3
------------------------------------------------------------
set linesize 150;
column n_region format a15;
column m_desc format a15;
column t_cal_month_desc format a17;
column p_cat format a17;

SELECT substr(p_cat,1,8), n_region, sales
FROM   (SELECT   p_cat, n_region,
                 SUM(s_amnt_sold) AS sales,
                 MAX(SUM(s_amnt_sold)) OVER (PARTITION BY p_cat)
                   AS MAX_REG_SALES
        FROM     bi.spctmn
        WHERE    s_t_id = TO_DATE('11-OCT-2001')
        GROUP BY p_cat, n_region)
WHERE  sales = MAX_REG_SALES;

quit;


-- P_CAT     N_REGION       SALES
-- --------  ------------ ----------
-- Electron  Americas      581.92
-- Hardware  Americas      925.93
-- Peripher  Europe       4290.38
-- Software  Americas      4445.7



-- order by does not reset the max

SELECT   substr(p_cat,1,8), n_region,
         SUM(s_amnt_sold) AS sales,
         MAX(SUM(s_amnt_sold)) OVER (ORDER BY p_cat desc)
           AS MAX_ORD_REG_SALES,
         MAX(SUM(s_amnt_sold)) OVER (PARTITION BY p_cat)
           AS MAX_PAR_REG_SALES
FROM     bi.spctmn
WHERE    s_t_id = TO_DATE('11-OCT-2001')
GROUP BY p_cat, n_region;


-- SUBSTR(P  N_REGION         SALES MAX_ORD_REG_SALES MAX_PAR_REG_SALES
-- --------  ----------- ---------- ----------------- -----------------
-- Software  Americas        4445.7            4445.7            4445.7
-- Software  Asia           1408.19            4445.7            4445.7
-- Software  Europe         3288.83            4445.7            4445.7
-- Software  Oceania         890.25            4445.7            4445.7
-- Peripher  Americas       3084.48            4445.7           4290.38
-- Peripher  Asia           2616.51            4445.7           4290.38
-- Peripher  Europe         4290.38            4445.7           4290.38
-- Peripher  Oceania         940.43            4445.7           4290.38
-- Hardware  Americas        925.93            4445.7            925.93
-- Electron  Americas        581.92            4445.7            581.92

------------------------------------------------------------
-- Reporting example 4
------------------------------------------------------------
set linesize 150;
column p_cat format a15;
column p_subcat format a15;
column m_desc format a15;
column t_cal_month_desc format a17;

SELECT substr(p_cat,1,8) p_cat, substr(p_subcat,1,8) p_subcat, p_id, SALES 
FROM  (SELECT   p_cat, p_subcat, p_id,
         SUM(s_amnt_sold) AS SALES,
         SUM(SUM(s_amnt_sold)) OVER (PARTITION BY p_cat) AS CAT_SALES,
         SUM(SUM(s_amnt_sold)) OVER (PARTITION BY p_subcat) AS SUBCAT_SALES,
         RANK() OVER  (PARTITION BY p_subcat
                         ORDER BY SUM(s_amnt_sold) ) AS RANK_IN_LINE
       FROM     bi.spctmn
       WHERE    s_t_id = to_DATE('11-OCT-2000')
       GROUP BY p_cat, p_subcat, p_id
       ORDER BY p_cat, p_subcat) 
WHERE SUBCAT_SALES > 0.2*CAT_SALES 
AND   RANK_IN_LINE <= 5;

quit;


-- SUBSTR(P SUBSTR(P    P_ID      SALES
-- -------- -------- ---------- ----------
-- Peripher Printer      128     2104.9
-- Peripher Printer      127    2264.61
-- Peripher Printer      129   11606.65
-- Software Recordab     115     221.27
-- Software Recordab     118     239.12
-- Software Recordab     119     246.54
-- Software Recordab     117     260.28
-- Software Recordab     116     358.96
-- Software Recordab     124    1405.65
-- Software Recordab     123    3032.13

------------------------------------------------------------
-- Partial ROLLUP example
------------------------------------------------------------
SELECT   m_desc, t_cal_month_desc, n_iso_code, 
	 SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10')
AND      n_iso_code IN ('GB', 'US')
GROUP BY m_desc, ROLLUP(t_cal_month_desc, n_iso_code);

quit;
------------------------------------------------------------
-- ROLLUP example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   m_desc, t_cal_month_desc, n_iso_code, 
	 SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10')
AND      n_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(m_desc, t_cal_month_desc, n_iso_code);

quit;
------------------------------------------------------------
-- ROW_NUMBER
------------------------------------------------------------
SET linesize 150;
column c_id format a15;
column p_id format a15;

SELECT	 m_desc, t_cal_month_desc AS month,
	 SUM(s_amnt_sold),
	 ROW_NUMBER() OVER (ORDER BY SUM(s_amnt_sold) DESC) AS Row_Number
FROM   	 bi.spctmn
WHERE	 t_cal_month_desc IN ('2001-09', '2001-10')
GROUP BY m_desc, t_cal_month_desc;

quit;
-- Ranking example in lecture notes, slide 69

set linesize 150;
column m_desc format a15;
column t_cal_month_desc format a17;

SELECT   m_desc, t_cal_month_desc, 
         COUNT(*) OVER (ORDER BY SUM(s_amnt_sold) DESC ROWS UNBOUNDED PRECEDING) as RANK
FROM     bi.spctmn
WHERE    t_cal_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
AND      m_desc IN ('Direct Sales', 'Internet')
GROUP BY m_desc, t_cal_month_desc;

quit;

-- M_DESC               T_CAL_MO       RANK
-- -------------------- -------- ----------
-- Direct Sales         2000-08           1
-- Direct Sales         2000-10           2
-- Direct Sales         2000-09           3
-- Direct Sales         2000-11           4
-- Internet             2000-11           5
-- Internet             2000-10           6
-- Internet             2000-09           7
-- Internet             2000-08           8
------------------------------------------------------------
-- Reporting example 1
------------------------------------------------------------
set linesize 150;
column m_desc format a15;
column t_cal_month_desc format a17;

SELECT *
FROM   (SELECT m_desc, SUM(s_amnt_sold) mtot,
               SUM(SUM(s_amnt_sold)) OVER () gtot
        FROM   bi.spctmn
        GROUP BY m_desc)
WHERE  mtot/gtot > 1/3;


SELECT *
FROM   (SELECT m_desc, SUM(s_amnt_sold) mtot,
               SUM(SUM(s_amnt_sold)) OVER () gtot,
               RATIO_TO_REPORT(SUM(s_amnt_sold)) OVER () ratio
        FROM   bi.spctmn
        GROUP BY m_desc)
WHERE  ratio > 0.05;


-- M_DESC                MTOT       GTOT
-- --------------- ---------- ----------
-- Direct Sales    57875260.6 98205831.2


-- M_DESC                MTOT       GTOT      RATIO
-- --------------- ---------- ---------- ----------
-- Direct Sales    57875260.6 98205831.2 .589326111
-- Internet          13706802 98205831.2 .139572181
-- Partners        26346342.3 98205831.2 .268276761


-- -- The following does not work since reporting functions (nested
-- -- aggregations) are evaluated last, and con only be used in the
-- -- select clause.

-- SELECT m_desc, SUM(s_amnt_sold) mtot,
--                SUM(SUM(s_amnt_sold)) OVER () gtot
-- FROM   bi.spctmn
-- GROUP BY m_desc
-- HAVING SUM(s_amnt_sold) / 3 > SUM(SUM(s_amnt_sold)) OVER ();


quit;
-- Reporting example in lecture notes, page 76

set linesize 150;
column m_desc format a15;
column t_cal_month_desc format a17;

SELECT   c_id, t_cal_month_desc, SUM(s_amnt_sold),
         AVG(SUM(s_amnt_sold)) OVER (ORDER BY t_cal_month_desc
                                     ROWS 2 PRECEDING) AS MOVING_3_MONTH_AVG
FROM     bi.spctmn
WHERE    t_cal_year=1999 
AND      c_id IN (6510,6230)
GROUP BY c_id, t_cal_month_desc
ORDER BY c_id, t_cal_month_desc; 

quit;

--       C_ID T_CAL_MO SUM(S_AMNT_SOLD) MOVING_3_MONTH_AVG
-- ---------- -------- ---------------- ------------------
--       6510 1999-04         124.69             124.69
--       6510 1999-05        3395.39            1760.04
--       6510 1999-06        4080.31         2533.46333
--       6510 1999-07        6434.63         4636.77667
--       6510 1999-08        5104.73         5206.55667
--       6510 1999-09        4676.23         5405.19667
--       6510 1999-10        5108.68         4963.21333
--       6510 1999-11         801.64            3528.85

------------------------------------------------------------
-- Reporting example 3
------------------------------------------------------------
set linesize 150;
column n_region format a15;
column m_desc format a15;
column t_cal_month_desc format a17;
column p_cat format a17;

SELECT substr(p_cat,1,8), n_region, sales
FROM   (SELECT   p_cat, n_region,
                 SUM(s_amnt_sold) AS sales,
                 MAX(SUM(s_amnt_sold)) OVER (PARTITION BY p_cat)
                   AS MAX_REG_SALES
        FROM     bi.spctmn
        WHERE    s_t_id = TO_DATE('11-OCT-2001')
        GROUP BY p_cat, n_region)
WHERE  sales = MAX_REG_SALES;

quit;


-- P_CAT     N_REGION       SALES
-- --------  ------------ ----------
-- Electron  Americas      581.92
-- Hardware  Americas      925.93
-- Peripher  Europe       4290.38
-- Software  Americas      4445.7



-- order by does not reset the max

SELECT   substr(p_cat,1,8), n_region,
         SUM(s_amnt_sold) AS sales,
         MAX(SUM(s_amnt_sold)) OVER (ORDER BY p_cat desc)
           AS MAX_ORD_REG_SALES,
         MAX(SUM(s_amnt_sold)) OVER (PARTITION BY p_cat)
           AS MAX_PAR_REG_SALES
FROM     bi.spctmn
WHERE    s_t_id = TO_DATE('11-OCT-2001')
GROUP BY p_cat, n_region;


-- SUBSTR(P  N_REGION         SALES MAX_ORD_REG_SALES MAX_PAR_REG_SALES
-- --------  ----------- ---------- ----------------- -----------------
-- Software  Americas        4445.7            4445.7            4445.7
-- Software  Asia           1408.19            4445.7            4445.7
-- Software  Europe         3288.83            4445.7            4445.7
-- Software  Oceania         890.25            4445.7            4445.7
-- Peripher  Americas       3084.48            4445.7           4290.38
-- Peripher  Asia           2616.51            4445.7           4290.38
-- Peripher  Europe         4290.38            4445.7           4290.38
-- Peripher  Oceania         940.43            4445.7           4290.38
-- Hardware  Americas        925.93            4445.7            925.93
-- Electron  Americas        581.92            4445.7            581.92

------------------------------------------------------------
-- Reporting example 4
------------------------------------------------------------
set linesize 150;
column p_cat format a15;
column p_subcat format a15;
column m_desc format a15;
column t_cal_month_desc format a17;

SELECT substr(p_cat,1,8) p_cat, substr(p_subcat,1,8) p_subcat, p_id, SALES 
FROM  (SELECT   p_cat, p_subcat, p_id,
         SUM(s_amnt_sold) AS SALES,
         SUM(SUM(s_amnt_sold)) OVER (PARTITION BY p_cat) AS CAT_SALES,
         SUM(SUM(s_amnt_sold)) OVER (PARTITION BY p_subcat) AS SUBCAT_SALES,
         RANK() OVER  (PARTITION BY p_subcat
                         ORDER BY SUM(s_amnt_sold) ) AS RANK_IN_LINE
       FROM     bi.spctmn
       WHERE    s_t_id = to_DATE('11-OCT-2000')
       GROUP BY p_cat, p_subcat, p_id
       ORDER BY p_cat, p_subcat) 
WHERE SUBCAT_SALES > 0.2*CAT_SALES 
AND   RANK_IN_LINE <= 5;

quit;


-- SUBSTR(P SUBSTR(P    P_ID      SALES
-- -------- -------- ---------- ----------
-- Peripher Printer      128     2104.9
-- Peripher Printer      127    2264.61
-- Peripher Printer      129   11606.65
-- Software Recordab     115     221.27
-- Software Recordab     118     239.12
-- Software Recordab     119     246.54
-- Software Recordab     117     260.28
-- Software Recordab     116     358.96
-- Software Recordab     124    1405.65
-- Software Recordab     123    3032.13