Get last 3 month average on daily data in SQL-CodePudding

I have a sales dataset where I need to calculate last 3 month sales average for each product entry.

This is what it is supposed to look like. So for a September entry for a given store, sales rep, product combination , L3M should be Sales of (June July August) / 3, if for a given rep there's no data entries for say July then it should just be (June August) / 2.

The same sql query needs to calculate 'Sales' on a daily level, and have a window function that calculates last 3 month average for the corresponding sales entry. How can i achieve this?

Here's a sample table

CREATE TABLE SalesData  (
  the_date Date,
  cal_month varchar(255),
  region_code varchar(255),
  store_id varchar(255),
  sales_rep varchar(255),
  product_id varchar(255),
  sales_amt float
);

and some sample data

insert into SalesData (the_date, cal_month, region_code, store_id, sales_rep, product_id, sales_amt) 
values ('2022-06-03', '202206', 'USNE', '4123', '65','1','50'),
       ('2022-06-19', '202206', 'USNE', '4123', '65','1','10'),
       ('2022-06-27', '202206', 'USNE', '4123', '65','2','60'),
       ('2022-07-02', '202207', 'USNE', '4123', '65','1','00'),
       ('2022-08-05', '202208', 'USNE', '4123', '65','1','30'),
       ('2022-08-09', '202208', 'USNE', '4123', '65','2','10'),
       ('2022-08-06', '202208', 'USNE', '5646', '32','1','100'),
       ('2022-08-06', '202208', 'USNE', '5646', '32','2','120'),
       ('2022-09-03', '202209', 'USNE', '4123', '65','1','70'),
       ('2022-09-07', '202209', 'USNE', '4123', '65','2','20'),
       ('2022-09-07', '202209', 'USNE', '5646', '32','2','30');

So, here for September entries the L3M column will be calculated like this:

For product 1 for sales rep 65, L3M should be (sum(june) sum(july) sum(august))/3 = (60 0 30) / 3 = 30

and for product 2 (sales rep 65), L3M should be (60 10) / 2 = 35

and for product 2 (sales rep 32), L3M should be 120 / 1 = 120

CodePudding user response：

So you can do this by simply left joining the table on itself by product_id and sales_rep and where the_date is within the target range.

Your target range is can be given as between dateadd(month, -3, dateadd(day, 1-datepart(day, the_date), the_date)) and dateadd(day, -datepart(day, the_date), the_date).

SELECT A.the_date, A.cal_month, A.region_code
    , A.store_id, A.sales_rep, A.product_id, A.sales_amt
    , coalesce(sum(B.sales_amt), 0) as total_sales
    , count(distinct B.cal_month) as months
    , case when count(distinct B.cal_month) = 0 then 0
        else sum(B.sales_amt) / count(distinct B.cal_month) end as L3M
FROM SalesData  AS A
LEFT JOIN SalesData AS B
    ON A.sales_rep = B.sales_rep 
    AND A.product_id = B.product_id 
    AND B.the_date 
      BETWEEN dateadd(month, -3, dateadd(day, 1-datepart(day, A.the_date), A.the_date)) 
      AND dateadd(day, -datepart(day, A.the_date), A.the_date)
GROUP BY A.the_date, A.cal_month, A.region_code
       , A.store_id, A.sales_rep, A.product_id, A.sales_amt
ORDER BY A.the_date