CODEWITHSUNDEEP
sql
ExplodingPony
ExplodingPony

Reputation: 57

how to calculate trend based on events

I've got to try and calculate a trend for customers, based on the following:

any 2 instances of 0 sales in a 4 week period.

For example -

enter image description here

Between weeks 27 and 36, this specific store has 0 sales between weeks 27 & 32. Sales are then made between week 33 & 35, 0 sales in the final week.

My full data set contains multiple stores for each Person (cut down for clarity in the explanation).

Result I'm after

I need a sum by person, by week, of all stores that had 2 instances of 0 sales in the last 4 weeks.

So for this store, I'd expect to see a 1 value for Weeks 27 to week 34, as for each week in this period, that week and previous 3 weeks have at least 2 instances of 0 sales.

Week 35 and 36 should be 0, as these 2 weeks both have sales for at least 3 out of the 4 weeks.

I've got this so far but it's not quite right...

http://sqlfiddle.com/#!18/586cf9/10

WITH 
SourceData AS (
SELECT CustomerId, Person, Year, Week, 
TotalSales, 

CASE WHEN TotalSales = 0 THEN 1 ELSE 0 END AS ZeroSale,
  LAG(
        CASE WHEN TotalSales = 0 THEN 1 ELSE 0 END, 4) 
      OVER (Partition By customerId order by year, week ) AS Lagg
  
FROM sales
)

SELECT CustomerId, Person, Year, Week, CAST(totalSales AS int) AS TotalSales, ZeroSale, lagg, 
        
        CASE WHEN SUM (lagg )<=2 THEN 1 ELSE 0 END AS includedinfigures

FROM SourceData

GROUP BY CustomerId, Person, Year, Week, CAST(totalSales AS int), ZeroSale, lagg

Any help would be appreciated...

Upvotes: 1

Views: 182

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If I understand correctly, you want a flag based on the cumulative count (in the previous four rows) of zero sales. If so, you can use window functions:

select s.*,
       (case when sum(case when TotalSales = 0 then 1 else 0 end) over
                      (partition by customerid, person
                       order by year, week
                       rows between 3 preceding and current row
                      ) >= 2
             then 1 else 0
        end) as flag
from Sales s;

Here is a db<>fiddle.

Upvotes: 1

Related Questions