CASE WHEN - Multiple Conditions - Over Multiple Rows of same reference

Question

I am trying to create a flag by using CASE WHEN to determine something but it keeps returning "single_state" - I guess because its not applying the CASE WHEN to all rows with o/n 1234 which is the bit I need help with.

My expected output is to have "multi_state" for the first 3 lines as they are all the same o/n and "single_state" for the last line as its neither grouped or got more than 1 state.

Example below:

DATA Table

o/n      sku     order_type    state   state_check
1234    ABC987    Grouped     express
1234    BCD654    Grouped     arrived
1234    CDF321    Grouped     shipped
5679    GBT104   Not Grouped  express

Query

SELECT o/n, SKU, order_type, state, 
CASE WHEN order_type is "Grouped" AND (state = "express" AND state = "arrived") THEN "multi_state"
CASE WHEN order_type is "Grouped" AND (state = "express" AND state = "shipped") THEN "multi_state"
ELSE "single_state"
END AS state_check
FROM data.table

Answer 1

You need a window function (an aggregation function with an OVER clause) to look at multiple rows at once.

SELECT 
  "o/n", sku, order_type, state,
  CASE
    WHEN order_type <> 'Grouped' THEN
      'single_state'
    WHEN COUNT(DISTINCT state) OVER (PARTITION BY "o/n", order_type) = 1 THEN
      'single_state'
    ELSE
      'multi_state'
  END
FROM data.table
ORDER BY "o/n", sku;