vendredi 15 décembre 2017

Using GROUP BY/CASE with WHEN or IF

I am looking to segment a data set according to two criteria:

  1. If a customer has more or less than 4 txns at a specific restaurant
  2. If a customer has more or less than 24 txns at all the other restaurants in that data set.

I am using a conjunction of GROUP BY, CASE and WHEN or IF. I am not sure which approach is best, if either?

GROUP BY CASE
WHEN(COUNT('MerchantFamily' = %x%)> 2) AND COUNT('MerchantFamily' != %x%) >24) THEN 'Fanatic'
WHEN(COUNT('MerchantFamily' = %x%)> 2) AND COUNT('MerchantFamily' != %x%) <24) THEN 'Loyalist'
WHEN(COUNT('MerchantFamily' = %x%)< 2) AND COUNT('MerchantFamily' != %x%) <24) THEN 'Seldom'
ELSE 'Potential'    
END

OR

GROUP BY CASE
IF(COUNT(IF('MerchantFamily' = 'x',1,0))>2,TRUE,FALSE) AND IF(IFCOUNT(IF('MerchantFamily' =! 'x',1,0))<24,TRUE,FALSE), 'Loyalist', NULL)
IF(COUNT(IF('MerchantFamily' = 'x',1,0))>2,TRUE,FALSE) AND IF(IFCOUNT(IF('MerchantFamily' =! 'x',1,0))>24,TRUE,FALSE), 'Fanatic', NULL) 
IF(COUNT(IF('MerchantFamily' = 'x',1,0))<2,TRUE,FALSE) AND IF(IFCOUNT(IF('MerchantFamily' =! 'x',1,0))<24,TRUE,FALSE), 'Seldom', NULL) 
ELSE 'Potential'
END

Aucun commentaire:

Enregistrer un commentaire