Check out the annotated script but the general steps are:
create temp table that contins the min date for each product (aka date of the first sale)
another table that contains all possible combinations between dates and products
join both temp tables back to the raw data table
exclude rows for which the sale date is less than or equal to the min sale date (Where Date >= MinDate).
at this point we have the data ready and can make the calculation that will give us the aggregated sale
Annotated script:
RawData:
Load * inline [
Date, Product, Sold
01/01/2020, a, 5
01/01/2020, b, 12
01/01/2020, z, 9
01/01/2020, k, 18
06/01/2020, a, 18
07/01/2020, b, 40
07/01/2020, l, 5
07/01/2020, k, 9
11/01/2020, m, 5
];
// find the first sale date for each product
MinDates:
Load
Date(min(Date), 'DD/MM/YYYY') as MinDate,
Product
Resident
RawData
Group By
Product
;
// create table with all possible dates and products
TempTable1:
Load Distinct
Date
Resident
RawData
;
join
Load Distinct
Product
Resident
RawData
;
// join both temp tables to the main data table
// and drop the temp tables
join (RawData)
Load * Resident TempTable1;
Drop Table TempTable1;
join (RawData)
Load * Resident MinDates;
Drop Table MinDates;
// filter out records for which the sale date is
// less than or equal of the first sale date
NoConcatenate
RawData1:
Load
*
Resident
RawData
Where
Date >= MinDate
;
Drop Table RawData;
// once we have the data in the right format we can calculate aggregated sale amount
// for each record:
// * if the current Product value is not equal to the previous one - get the Sold amount
// * else if Sold amount is not null - get the current Sold amound and add the previous AggregatedSale amount
// * else get the previous AggregatedSale amount
NoConcatenate
FinalTalbe:
Load
Date,
Product,
Sold,
if(Product <> peek(Product), Sold,
if(Sold <> null(), Sold + peek(AggregatedSale), peek(AggregatedSale))
) as AggregatedSale
Resident
RawData1
Order By
Product,
Date
;
Drop Table RawData1;
