avg sale of quarter with previous quarter sale

Database
Enthusiast

avg sale of quarter with previous quarter sale

I have a table t in which there are various attribute like product,year,qtr,month,sale. I have to calculate the avg_qtr sale and show previous avg_qtr sale. I have used windowing function to show previous avg_qtr sale. I have read about lag but here it is not possible to use as it is not fixed after how many rows it will be repeated. My table structure is like this-

Product          Year      Qtr                    Month             Sales
P1
2013 1 JAN 2000
P2
2013 2 APR 3000
P3
2013 3 JUL 4000
P4
2013 4 OCT 5000
P2
2014 1 FEB 6000
P3
2014 2 MAY 8000
P1
2014 3 AUG 5000
P4
2014 4 NOV 6000
P4
2015 1 MAR 7000
P1
2015 2 JUN 1000
P1
2015 3 SEP 2000
P3
2015 4 DEC 3000
P1
2013 1 JAN 5000
P2
2013 2 APR 2000
P3
2013 3 JUL 5000
P4
2013 4 OCT 6000
P1
2014 1 FEB 7000
P2
2014 2 MAY 1000
P3
2014 3 AUG 2000
P4
2014 4 NOV 3000
P1
2015 1 MAR 5000
P2
2015 2 JUN 2000
P3
2015 3 SEP 3000
P4
2015 4 DEC 5000
P2
2013 1 JAN 2000
P1
2013 2 APR 3000
P4
2013 3 JUL 4000
P3
2013 4 OCT 5000

my query is-

select product,year,month,sales,qtr,av, lag (av) over (order by QTR) from (
select product,year,month,sales ,qtr,round (avg(sales) over (partition by qtr,year),2) as av from t

but as said it won't work in pre_avg_sale order by qtr,year); I want output in this format-

product        year    qtr       month     sales      qtr_avg  prv_qtr_avg

Actually I want previous avg_sale such as first quarter of 2014 contains in previous avg_Sale of last quarter of 2013 Is there is way to do this please help!!!

9 REPLIES
Enthusiast

Re: avg sale of quarter with previous quarter sale

Create Volatile table test 
AS
(
SEL
 PRODUCT
,YR
,QTR
,AVG ( SALES) AS AVG_SALE
,ROW_NUMBER () OVER (ORDER BY PRODUCT,YR,QTR) AS R_NUM
FROM  SANDBOX.TABLE_SRC
GROUP BY 1,2,3
) WITH DATA
ON COMMIT PRESERVE ROWS

SEL S.* , T.AVG_SALE, T2.AVG_SALE  AS PRV_QTR_AVG_SALE
FROM
SANDBOX.TABLE_SRC  S
INNER JOIN
TEST  T
ON  S.PRODUCT = T.PRODUCT
AND S.YR  = T.YR
AND S.QTR   = T.QTR 
LEFT  JOIN
TEST  T2
ON  T.PRODUCT = T2.PRODUCT
AND T.R_NUM -1 = T2.R_NUM
ORDER BY S.YR, S.QTR

Please try this option.

Enthusiast

Re: avg sale of quarter with previous quarter sale

can you expalin what exactly you are doing...I am not undersatnding how this will work...I want to know the approch

Enthusiast

Re: avg sale of quarter with previous quarter sale

when I do left join then how can I do on that condition

T.R_NUM -1 = T2.R_NUM 

this column is not in test it is in another table

Enthusiast

Re: avg sale of quarter with previous quarter sale

I understand the above join condition bt the data I want is not that I am getting also many products are shown dupliacte, also row_number-1 that condition is not working correctly 

Enthusiast

Re: avg sale of quarter with previous quarter sale

anyone can please help me...this is really important for me...I have heard that this forum reply fast to your solution...I need help really :(

Enthusiast

Re: avg sale of quarter with previous quarter sale

CREATE VOLATILE TABLE TEST 
AS
(
SEL
 PRODUCT
,YR
,QTR
,AVG ( SALES) AS AVG_SALE
,ROW_NUMBER () OVER (ORDER BY YR,QTR) AS R_NUM
FROM  SANDBOX.TEST_SKV
GROUP BY 1,2,3
) WITH DATA
ON COMMIT PRESERVE ROWS

-- On this step we create table to store average at product, year, Qtr level
Also we store the row number at Year quarter level

SEL
  S.PRODUCT
, S.YR, S.QTR
, S.AVG_SALE
, S1.AVG_SALE AS PRV_AVG_SALE
FROM
(
  SEL
   T1.*
  ,MAX(T2.R_NUM) AS PRV_RNUM
  FROM TEST T1
  LEFT JOIN TEST T2
  ON T1.PRODUCT = T2.PRODUCT
  AND T1.R_NUM >  T2.R_NUM
  GROUP BY 1,2,3,4,5
) S
-- this derived table S stores the row number of prev quarter for -- that product, only glitch here is if the previous qtr has no
-- sales for the product it will go and search next qtr in prev.
 LEFT JOIN test S1
ON S.PRV_RNUM = S1.R_NUM
ORDER BY 1,2,3

-- In total this query gives the summary information of avg sale 
-- ,prev average sales at product, year, qtr, grain

-- If this this doesn't solve your problem, please give sample
-- data and your expected output.
Enthusiast

Re: avg sale of quarter with previous quarter sale

--handles when the current quarter being viewed is 2,3,or 4 because those would still be in the same year when looking at the previous quarter
select t1.product,
t1
.year,
t1
.month,
t1
.sales ,
t1
.qtr,
round
(avg(t1.sales) over (partition by t1.qtr,t1.year),2) as av,
t2
.prev_av
from one t1
left join ( select
product
,
year
,
month
,
sales
,
qtr
,
round
(avg(sales) over (partition by qtr,year),2) as prev_av
from one
) t2
on t1.year = t2.year
and (t1.qtr - 1) = t2.qtr
where t1.qtr in (2,3,4)
union
--handles the 1st quarter of the year when you need to grab the 4th quarter of the previous year for the previous avg
select t3.product,
t3
.year,
t3
.month,
t3
.sales ,
t3
.qtr,
round
(avg(t3.sales) over (partition by t3.qtr,t3.year),2) as av,
t4
.prev_av
from one t3
left join ( select
product
,
year
,
month
,
sales
,
qtr
,
round
(avg(sales) over (partition by qtr,year),2) as prev_av
from one
) t4
on (t3.year - 1) = t4.year
and t4.qtr = 4
where t3.qtr = 1;

I want something like this but its too heavy but I cant eliminate select statements can you?

Enthusiast

Re: avg sale of quarter with previous quarter sale

The glitch in your code is what I don't want as it will chnage my ouput I want output like this-

quarter  year    current_avg      previous_avg
1 2013 2000 (null) (as there no entrey before that
2 2013 1000 2000
3 2013 3000 1000
4 2013 2000 3000
1 2014 4000 2000

Enthusiast

Re: avg sale of quarter with previous quarter sale

WITH AvgSales

AS (SELECT

region,

product,

year,

qtr,

ROUND(AVG(sales), 2) AS avg_Sale

FROM one

GROUP BY region,

product,

year,qtr

 )

SELECT

s.region,

s.product,

s.year,

s.month,

s.sales,

avg.qtr,

avg.avg_Sale AS Qtr_Avg_Sale,

prev.avg_sale AS Prev_Qtr_Avg_Sale

FROM one s

JOIN AvgSales avg

ON s.region = avg.region

AND s.product = avg.product

AND s.QTR = avg.qtr

AND s.year = avg.year

LEFT JOIN AvgSales prev

ON  (s.region = prev.region

AND s.product = prev.product

AND s.year - 1 = prev.year

AND prev.qtr = 4) or

(s.region = prev.region

AND s.product = prev.product

AND s.year = prev.year

AND s.qtr - 1 = prev.qtr) ;

I have made this and its getting me right result but I am not sure will this condition runs always correct as in this condition it is also satisfying for quarter 2,3,4

ON  (s.region = prev.region

AND s.product = prev.product

AND s.year - 1 = prev.year

AND prev.qtr = 4)