Weekly sales for different start dates

Database
Enthusiast

Weekly sales for different start dates

Hi everyone,
I’m trying to develop a report with customer sales behaviour each time a new product is Launched.
The columns in the report would be : sales in time t (the time product was launched ) , sales in time t-1 (week before) , sales in time (t-2) (2 weeks before) and sales in reporting week (this report will be refreshed every week)
I have a table with :
Product id, day launched

Then I have transactional data

My problem is that time t is different for each product so i am not really aware how can I make this in a optimized way..

Can you please help me? If you didn’t get my question please let me know so I can explain it better !

Accepted Solutions
Teradata Employee

Re: Weekly sales for different start dates

Ok I've done some adaptation but I don't see any flaw in my logic.

Let the pivot aside for now, just focus on the comparaison between sales and launch dates :

    select prd.product_id
         , prd.launch_date
         , sal.sale_date
         , trunc(prd.launch_date, 'iw') as launch_week 
         , trunc(sal.sale_date, 'iw')   as sale_week
         , coalesce(sal.sale_amount, 0) as sale_amount
         , case
             when trunc(sal.sale_date, 'iw') < trunc(prd.launch_date, 'iw') - 14 then 't--'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') - 14 then 't-2'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') -  7 then 't-1'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw')      then 't'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') +  7 then 't+1'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') + 14 then 't+2'
             when trunc(sal.sale_date, 'iw') > trunc(prd.launch_date, 'iw') + 14 then 't++'
             else 't_nul'
           end as category
      from mvt_products as prd
 left join mvt_sales    as sal  on sal.product_id = prd.product_id
  order by prd.product_id asc
         , sal.sale_date  asc;

 product_id launch_date sale_date  launch_week sale_week  sale_amount category 
 ---------- ----------- ---------- ----------- ---------- ----------- -------- 
          1 2018-12-15  2018-01-10 2018-12-10  2018-01-08        2.00 t--     
          1 2018-12-15  2018-12-17 2018-12-10  2018-12-17        5.00 t+1     
          1 2018-12-15  2018-12-19 2018-12-10  2018-12-17        4.00 t+1     
          1 2018-12-15  2018-12-27 2018-12-10  2018-12-24        3.00 t+2     
          1 2018-12-15  2019-01-15 2018-12-10  2019-01-14        7.00 t++     
          2 2018-12-25  2018-12-25 2018-12-24  2018-12-24        9.00 t       
          2 2018-12-25  2019-01-01 2018-12-24  2018-12-31        6.00 t+1     
          3 2019-01-25  {null}     2019-01-21  {null}            0.00 t_nul   

Do you see any sales not in the right category ?

1 ACCEPTED SOLUTION
9 REPLIES
Junior Contributor

Re: Weekly sales for different start dates

How do you define week, a calendar week (Sun to Sat or Mon to Sun) or 7 days (launch  date - 6 to launch date)?

How do you want to show the launch date in your report?

Is this for a single product or a list of products?

Enthusiast

Re: Weekly sales for different start dates

Hi dnoeth,

 

A week is from Monday to Sunday.

The report will have the following columns:

product_iddate_launchedt-2t-1tt+1t+2….

And i have a list of products with different launched dates!

 

Thank you!

Teradata Employee

Re: Weekly sales for different start dates

Hi ApprenticeVS,

 

You have to make a join between your tables, use a case to compare the launch and sale dates to attribute them a generic value of t-2, t-1, t...

Then you have to pivot the data.

 

Datas

create multiset volatile table mvt_products, no log
( product_id    integer
, launch_date   date
)
unique primary index (product_id)
on commit preserve rows;

insert into mvt_products values (1, date '2018-12-15');
insert into mvt_products values (2, date '2018-12-25');
insert into mvt_products values (3, date '2019-01-25');


create multiset volatile table mvt_sales, no log
( product_id    integer
, sale_date     date
, sale_amount   decimal(5,2)
)
primary index (product_id)
on commit preserve rows;

insert into mvt_sales values (1, date '2018-01-10', 2);
insert into mvt_sales values (1, date '2018-12-17', 5);
insert into mvt_sales values (1, date '2018-12-19', 4);
insert into mvt_sales values (1, date '2018-12-27', 3);
insert into mvt_sales values (1, date '2019-01-15', 7);
insert into mvt_sales values (2, date '2018-12-25', 9);
insert into mvt_sales values (2, date '2019-01-01', 6);

Query

with cte_set_time as
(
    select prd.product_id
         , prd.launch_date
         , sal.sale_date
         , coalesce(sal.sale_amount, 0) as sale_amount
         , case
             when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') - 14 then 't--'
             when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') -  7 then 't-2'
             when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw')      then 't-1'
             when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') +  7 then 't'
             when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') + 14 then 't+1'
             when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') + 21 then 't+2'
             when trunc(sal.sale_date, 'iw') >  trunc(prd.launch_date, 'iw') + 21 then 't++'
             else 't_nul'
           end as category
      from mvt_products as prd
 left join mvt_sales    as sal on sal.product_id = prd.product_id
)
  select product_id
       , launch_date
       , sum(case category when 't--'   then sale_amount else 0 end) as "t--"
       , sum(case category when 't-2'   then sale_amount else 0 end) as "t-2"
       , sum(case category when 't-1'   then sale_amount else 0 end) as "t-1"
       , sum(case category when 't'     then sale_amount else 0 end) as "t"
       , sum(case category when 't+1'   then sale_amount else 0 end) as "t+1"
       , sum(case category when 't+2'   then sale_amount else 0 end) as "t+2"
       , sum(case category when 't++'   then sale_amount else 0 end) as "t++"
       , sum(case category when 't_nul' then 1           else 0 end) as "t_nul"
    from cte_set_time
group by product_id
       , launch_date
order by product_id asc;

 product_id launch_date t--  t-2  t-1  t    t+1  t+2  t++  t_nul 
 ---------- ----------- ---- ---- ---- ---- ---- ---- ---- ----- 
          1 2018-12-15  2.00 0.00 0.00 9.00 3.00 0.00 7.00     0
          2 2018-12-25  0.00 0.00 9.00 6.00 0.00 0.00 0.00     0
          3 2019-01-25  0.00 0.00 0.00 0.00 0.00 0.00 0.00     1

 

 

Enthusiast

Re: Weekly sales for different start dates

Hi Waldar,

 

Thank you for the reply i think it's almost there but the t-2,t-3...it's not commulative..is t-1=week before launch; t-2=week before the week before launch

and also, how can i include in where clause a minimumand maximum date (which is the same as the day of analysis) so that i can take only sales from table mtv_sales  from days i need based on product launch dates and t-1,t-2,t+1,t+2....?

 

Thank you so much for the help so far!

 

Teradata Employee

Re: Weekly sales for different start dates

 

I don't get the cummulative thing, it's not in my answer, I only add the amounts within the same week.

Considering the data I provided, what do you expect as output ?

 

For the filter, you have to handle it at two places.

In the product table and in the sales tables.

 

For example, inside the cte_set_time :

      from mvt_products as prd
 left join mvt_sales    as sal  on sal.product_id = prd.product_id
                               and trunc(sal.sale_date, 'iw') >= trunc(date '2018-12-01', 'iw') - 14
                               and trunc(sal.sale_date, 'iw') <  trunc(date '2019-01-01', 'iw') + 21
     where prd.launch_date >= date '2018-12-01'
       and prd.launch_date <  date '2019-01-01'
Enthusiast

Re: Weekly sales for different start dates

I expect the following (as an example)

 

id_prodlaunch_datet--t-2t-1t
115/12/20182000
225/12/201820912

 

For example, for product 1 , sales on time t will be between '2018-12-10' and '2018-12-16' (Mon-Fri week of launch date). On time t-1 will be sales between '2018-12-03' and '2018-12-09' , on time t-2 will be sales between '2018-11-26' and '2018-12-02'.

 

 

Junior Contributor

Re: Weekly sales for different start dates

As truncating to week returns a single date per week this can be simplified avoiding the nested CASEs:

SELECT
   prd.product_id
  ,prd.launch_date
  ,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) - 14 THEN sale_amount ELSE 0 END) AS "t-2" 
  ,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) -  7 THEN sale_amount ELSE 0 END) AS "t-1"
  ,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date)      THEN sale_amount ELSE 0 END) AS "t"
  ,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) +  7 THEN sale_amount ELSE 0 END) AS "t+1"
  ,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) + 14 THEN sale_amount ELSE 0 END) AS "t+2"
  ,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) + 21 THEN sale_amount ELSE 0 END) AS "t+3"
FROM mvt_products AS prd
LEFT JOIN mvt_sales AS sal
  ON sal.product_id = prd.product_id
GROUP BY 1,2

I preferred Td_Monday over Trunc to get the previous Monday because it clearly states the week day and could be easily changed to other start days

 

 

Teradata Employee

Re: Weekly sales for different start dates

Ok I've done some adaptation but I don't see any flaw in my logic.

Let the pivot aside for now, just focus on the comparaison between sales and launch dates :

    select prd.product_id
         , prd.launch_date
         , sal.sale_date
         , trunc(prd.launch_date, 'iw') as launch_week 
         , trunc(sal.sale_date, 'iw')   as sale_week
         , coalesce(sal.sale_amount, 0) as sale_amount
         , case
             when trunc(sal.sale_date, 'iw') < trunc(prd.launch_date, 'iw') - 14 then 't--'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') - 14 then 't-2'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') -  7 then 't-1'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw')      then 't'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') +  7 then 't+1'
             when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') + 14 then 't+2'
             when trunc(sal.sale_date, 'iw') > trunc(prd.launch_date, 'iw') + 14 then 't++'
             else 't_nul'
           end as category
      from mvt_products as prd
 left join mvt_sales    as sal  on sal.product_id = prd.product_id
  order by prd.product_id asc
         , sal.sale_date  asc;

 product_id launch_date sale_date  launch_week sale_week  sale_amount category 
 ---------- ----------- ---------- ----------- ---------- ----------- -------- 
          1 2018-12-15  2018-01-10 2018-12-10  2018-01-08        2.00 t--     
          1 2018-12-15  2018-12-17 2018-12-10  2018-12-17        5.00 t+1     
          1 2018-12-15  2018-12-19 2018-12-10  2018-12-17        4.00 t+1     
          1 2018-12-15  2018-12-27 2018-12-10  2018-12-24        3.00 t+2     
          1 2018-12-15  2019-01-15 2018-12-10  2019-01-14        7.00 t++     
          2 2018-12-25  2018-12-25 2018-12-24  2018-12-24        9.00 t       
          2 2018-12-25  2019-01-01 2018-12-24  2018-12-31        6.00 t+1     
          3 2019-01-25  {null}     2019-01-21  {null}            0.00 t_nul   

Do you see any sales not in the right category ?

Highlighted
Enthusiast

Re: Weekly sales for different start dates

No, it is correct sorry it was my mistake when adapting to my data!

 

Thank you both for your help!!!!