Oracle Analytic Funcation: LAG() To Analyze Yearly Sales
The Oracle LAG() OVER(PARTITION BY ... ORDER BY ...) function is used to create current year to previous year data setup, which can used to analyze year over year growth in any data entity such as sales, quantity, customer count etc.

Annual Sales Analysis (YoY) 
-- When the data is not in order sequentially by any grouping identifier (year_id), the ORDER BY 
-- needs to be setup with
-- ORDER BY q_yoy.year_id.
 
SELECT q_yoy.year_id, q_yoy.yearly_sales curr_yr,  
  LAG(q_yoy.yearly_sales) OVER
   (PARTITION BY q_yoy.t_cnt
        ORDER BY q_yoy.year_id) prev_yr,  
  q_yoy.yearly_sales - LAG(q_yoy.yearly_sales)  
           OVER(PARTITION BY q_yoy.t_cnt
                    ORDER BY q_yoy.year_id) diff_amount,  
  ROUND(100*(q_yoy.yearly_sales/LAG(q_yoy.yearly_sales)  
     OVER(PARTITION BY q_yoy.t_cnt
              ORDER BY q_yoy.year_id)-1),3) diff_pct  
FROM
 (SELECT 
   q_sales_sum.year_id,  q_sales_sum.revenue  yearly_sales,  
   COUNT(*) OVER() t_cnt  
  FROM
   (SELECT 2007 year_id, 100100 revenue FROM DUAL UNION  
    SELECT 2008 year_id, 102300 revenue FROM DUAL UNION  
    SELECT 2009 year_id, 108950 revenue FROM DUAL UNION  
    SELECT 2011 year_id, 119750 revenue FROM DUAL UNION   -- 2011 before 2010
    SELECT 2010 year_id, 110960 revenue FROM DUAL UNION  
    SELECT 2012 year_id, 127190 revenue FROM DUAL
   ) q_sales_sum  
) q_yoy;


Annual Sales Analysis (YoY) 
SELECT q_yoy.year_id, q_yoy.yearly_sales curr_yr,  
  LAG(q_yoy.yearly_sales) OVER
   (PARTITION BY q_yoy.t_cnt
        ORDER BY q_yoy.yearly_sales) prev_yr,  
  q_yoy.yearly_sales - LAG(q_yoy.yearly_sales)  
           OVER(PARTITION BY q_yoy.t_cnt
                    ORDER BY q_yoy.yearly_sales) diff_amount,  
  ROUND(100*(q_yoy.yearly_sales/LAG(q_yoy.yearly_sales)  
     OVER(PARTITION BY q_yoy.t_cnt
              ORDER BY q_yoy.yearly_sales)-1),3) diff_pct  
FROM
 (SELECT 
   q_sales_sum.year_id,  q_sales_sum.revenue  yearly_sales,  
   COUNT(*) OVER() t_cnt  
  FROM
   (SELECT 2007 year_id, 100100 revenue FROM DUAL UNION  
    SELECT 2008 year_id, 102300 revenue FROM DUAL UNION  
    SELECT 2009 year_id, 108950 revenue FROM DUAL UNION  
    SELECT 2010 year_id, 110960 revenue FROM DUAL UNION  
    SELECT 2011 year_id, 119750 revenue FROM DUAL UNION  
    SELECT 2012 year_id, 127190 revenue FROM DUAL
   ) q_sales_sum  
) q_yoy;

Oracle Analytic Funcation LAG() to Analyze YoY Sales

  YTD Analysis   LEAD, LAG Analysis

Oracle registered trademark of Oracle Corporation.
Last Revised On: October 13th, 2020

  72520