Weekly sales for different start dates automate report

Question

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)

In Teradata dwh, 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 !

score 0 · Answer 1 · answered Jan 22 '19 at 13:34

Option 1:

This is the option I would choose, but I don't know if it is suitable in your case. I would at first query the data so that I get an intermediate data set with columns:

ProductId
DiffWeek (the information DateSold - DateLaunched in weeks, so t-x)
TotalQuantitySold (so the sales)

Here is an example, how this can be computed in SQL (I looked up the ROUND() and date difference calculation for Teradata SQL and because the rest I assume is Standard SQL, I assume this SQL code works in a Teradata DWH):

/*assuming product data i.e.*/
CREATE TABLE Product (ProductId INT, DateLaunched DATE)
INSERT INTO Product (ProductId, DateLaunched)
VALUES (1, '20190204'), (2, '20190211'), (3, '20190218')

/*assuming transactional data i.e.*/
CREATE TABLE Sale (SaleId INT, ProductId INT, QuantitySold INT, DateSold DATE)
INSERT INTO Sale (SaleId, ProductId, QuantitySold, DateSold)
VALUES (1, 1, 1, '20190129'), (2, 2, 2, '20190129'), (3, 2, 2, '20190129'), (4, 3, 3, '20190129'), (5, 3, 1, '20190129')

/*query to prepare data*/
/*make sure to declare indices for ProductId columns to improve performance*/
SELECT
    subquery.ProductId
    ,subquery.DiffWeek
    ,SUM(subquery.QuantitySold) AS TotalQuantitySold
FROM
    (SELECT
        s.SaleId
        ,s.ProductId
        ,s.QuantitySold
        ,s.DateSold
        ,p.DateLaunched
        ,ROUND((s.DateSold - p.DateLaunched) / 7, 0) AS DiffWeek
    FROM Sale AS s
    JOIN Product AS p ON s.ProductId = p.ProductId) AS subquery
GROUP BY subquery.ProductId, subquery.DiffWeek

I would store the output of this query in a table and access it with Ms Excel Power Query as described here. Next, I would use this data connection as a source for a Power Pivot Table as described here. In the Pivot Table, I would use ProductId as row headers, DiffWeek as column headers and TotalQuantitySold as values.

Option 2:

I didn't find that Teradata DWH SQL has a PIVOT()-function or equivalent like MSSQL does. So a not very elegant way to completely prepare your data for the report in Teradata DWH would be something like this:

/*query to get report data*/
/*make sure to declare indices for ProductId columns to improve performance*/
SELECT
    subquery1.ProductId
    ,SUM(subquery1.Sales_t) AS TotalSales_t
    ,SUM(subquery1.Sales_t_1) AS TotalSales_t_1 /*t-1*/
    ,SUM(subquery1.Sales_t_2) AS TotalSales_t_2 /*t-2*/
    ,SUM(subquery1.Sales_t_3) AS TotalSales_t_3 /*t-3*/
    /*, ...*/
FROM (
    SELECT
        subquery0.ProductId
        ,CASE WHEN subquery0.DiffWeek = 0
            THEN subquery0.QuantitySold ELSE 0 END AS Sales_t
        ,CASE WHEN subquery0.DiffWeek = -1 /*t-1*/
            THEN subquery0.QuantitySold ELSE 0 END AS Sales_t_1
        ,CASE WHEN subquery0.DiffWeek = -2 /*t-2*/
            THEN subquery0.QuantitySold ELSE 0 END AS Sales_t_2
        ,CASE WHEN subquery0.DiffWeek = -3 /*t-3*/
            THEN subquery0.QuantitySold ELSE 0 END AS Sales_t_3
        /*, ...*/
    FROM (
        SELECT
            s.SaleId
            ,s.ProductId
            ,s.QuantitySold
            ,s.DateSold
            ,p.DateLaunched
            ,ROUND((s.DateSold - p.DateLaunched) / 7, 0) AS DiffWeek
        FROM Sale AS s
        JOIN Product AS p ON s.ProductId = p.ProductId
        ) AS subquery0
    ) AS subquery1
GROUP BY subquery1.ProductId

Weekly sales for different start dates automate report

1 Answers1