Oracle Analytic LAG Function - To Analyze Two Year Sales
Step Three: This query uses Oracle SUM(...) syntax to compute running totals for current year (cy_ytd_sales), previous year (py_ytd_sales) sales and YTD growth (ytd_growth).

Oracle SUM()OVER (PARTITION BY ... ORDER BY ...)
SELECT
 q_final.year_id,
 q_final.cy_sales, q_final.cy_ytd_sales,
 q_final.py_sales, q_final.py_ytd_sales,
 q_final.mth_growth,
 ROUND(100*(q_final.mth_growth)/q_final.py_sales,2) "MONTHLY_%",
 q_final.ytd_growth,
 ROUND(100*(q_final.ytd_growth)/q_final.py_ytd_sales,2) "YEARLY_%"
FROM
(SELECT
 q_cy.year_id,
 q_cy.cy_sales,
 q_cy.cy_sales - q_cy.py_sales mth_growth,
 TO_NUMBER(DECODE(q_cy.cy_sales,NULL,NULL,
  SUM(q_cy.cy_sales) OVER
   (PARTITION BY SUBSTR(q_cy.year_id,1,4) ORDER BY SUBSTR(q_cy.year_id,5)))) cy_ytd_sales,
 q_cy.py_sales,
 SUM(q_cy.py_sales) OVER
   (PARTITION BY SUBSTR(q_cy.year_id,1,4) ORDER BY SUBSTR(q_cy.year_id,5)) py_ytd_sales,
 TO_NUMBER(DECODE(q_cy.cy_sales,NULL,NULL,
  SUM(q_cy.cy_sales - q_cy.py_sales) OVER
   (PARTITION BY SUBSTR(q_cy.year_id,1,4) ORDER BY SUBSTR(q_cy.year_id,5)))) ytd_growth
FROM
(SELECT
  q_dw.year_id,  q_dw.sales cy_sales,
  LAG(q_dw.sales) 
   OVER (PARTITION BY SUBSTR(q_dw.year_id,5) ORDER BY q_dw.year_id) py_sales
 FROM
  (SELECT 201201 year_id, 11000  sales FROM DUAL UNION
   SELECT 201202 year_id, 12000  sales FROM DUAL UNION
   SELECT 201203 year_id, 14000  sales FROM DUAL UNION
   SELECT 201204 year_id, 16000  sales FROM DUAL UNION
   SELECT 201205 year_id, 15000  sales FROM DUAL UNION
   SELECT 201206 year_id, 18000  sales FROM DUAL UNION
   SELECT 201207 year_id, 19500  sales FROM DUAL UNION
   SELECT 201208 year_id, 17000  sales FROM DUAL UNION
   SELECT 201209 year_id, 16000  sales FROM DUAL UNION
   SELECT 201210 year_id, 16500  sales FROM DUAL UNION
   SELECT 201211 year_id, 13000  sales FROM DUAL UNION
   SELECT 201212 year_id, 15500  sales FROM DUAL UNION
   SELECT 201301 year_id, 12000  sales FROM DUAL UNION
   SELECT 201302 year_id, 14000  sales FROM DUAL UNION
   SELECT 201303 year_id, 16000  sales FROM DUAL UNION
   SELECT 201304 year_id, 17000  sales FROM DUAL UNION
   SELECT 201305 year_id, 11000  sales FROM DUAL UNION
   SELECT 201306 year_id, 19500  sales FROM DUAL UNION
   SELECT 201307 year_id, null  sales FROM DUAL UNION
   SELECT 201308 year_id, null  sales FROM DUAL UNION
   SELECT 201309 year_id, null  sales FROM DUAL UNION
   SELECT 201310 year_id, null  sales FROM DUAL UNION
   SELECT 201311 year_id, null  sales FROM DUAL UNION
   SELECT 201312 year_id, null  sales FROM DUAL
  ) q_dw
 ) q_cy
 WHERE SUBSTR(q_cy.year_id,1,4) = 2013
) q_final;

Final Query Output:


Detail Query Output: Year over Year Analysis

Running Totals: Previous Step

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 08, 2013

  3772