Oracle Analytic LAG Function - To Analyze Two Year Sales
Step One: This query uses Oracle analytic function - LAG(value_expression, offset, default) to analyze year-over-year sales/revenue. The typical data warehouse data/query is setup using query table q_dw. The sales data of the year 2013 is analyzed against 2012 sales data ( final output ).

Oracle Analytic LAG Function 
SELECT q_dw.year_id,  q_dw.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;

Query Output: Sales Data Setup

Compute Growth and Running Totals: Next Step

Oracle registered trademark of Oracle Corporation.
Last Revised On: November 08, 2013

  74070