CODEWITHSUNDEEP
sqlhive
bruinfanfare
bruinfanfare

Reputation: 21

How to classify or group values based on prior day values?

I have a data set that repeats daily and shows sales. If a product is released on Day 1 and has between 1-5 sales AND also if on Day 2 it has between 10-50 sales, I want to classify it as "Limited Sales."

If a product is released on Day 1 and has over 1,000 sales and also if on Day 2 it has over 1,000 sales, I want to classify it as "Wide Sales."

How would I go about doing this in standard SQL?

I've tried using some workarounds using CASE WHEN, but I ultimately end up with issues because while I can classify the 1st column with an output, I can't get the 2nd column to have an output that is also based on the 1st output (e.g. Column 1 is TRUE, but Column 2 is FALSE. What I need is for Column 1 = TRUE and Column 2 = True.

Here's what a sample query would look like:

Table looks like this:

Columns: name, day_number, sales

1.  Jack | 1 | 5
2.  Jack | 2 | 10
3.  Mary | 1 | 1250
4.  Mary | 2 | 1500



SELECT name, 
       day_number, 
       sales,
       CASE
         WHEN day_number = 1
         AND sales >= 1
         AND sales <= 5
         THEN "LIMITED SALES"
         ELSE "WIDE SALES"
         END AS status_1,
       CASE
         WHEN day_number = 2
         AND sales >= 10
         AND sales <= 50
         THEN TRUE 
         ELSE FALSE 
         END AS status_2
FROM table

Unfortunately this isn't really going to get me what I want. At the end of the day, I would like to see results like:

1.  Jack | 1 | 5  | LIMITED SALES
2.  Jack | 2 | 10 | LIMITED SALES
3.  Mary | 1 | 1250 | WIDE SALES
4.  Mary | 2 | 1500 | WIDE SALES

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Is this what you want?

select name,
       (case when sum(case when day_number = 1 then sales end) between 1 and 5 and
                  sum(case when day_number = 2 then sales end) between 10 and 50
             then 'Limited Sales'
             when sum(case when day_number = 1 then sales end) > 1000 and
                  sum(case when day_number = 2 then sales end) > 1000
             then 'Wide Sales'
             else '???'
        end) as sales_category
from t
group by name

If you want this on each of the original rows, then use window functions or a join.

Upvotes: 1

Related Questions