I am looking to segment a data set according to two criteria:
- If a customer has more or less than 4 txns at a specific restaurant
- 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