0

I have a customer data set with the Customer ID and customer Spend. I need to split the customer dataset into 3 groups based on the customer send(high spend customers, Medium spend custs, low spend custs). I tried using RANK and Partitio by but i wasn't able to give the number of groups(3). Is there any other way I can do this with Teradata. If there are 9 customers, I need to first sort them all by the amount spent and then divide them into 3 groups and count how many customers are there under each group(3). Also determine the Min and max values of the amount spent under each group

Say, Cust F,G,I spent between $9 to $ 12 and are under high-spend Cust A,B,D spent between $4 to $8 and are in the medium spend Custs C,E,H spent between $1-$3 and are under low spend

The output should be GROUP(1,2,3) ; # Custs(3,3,3); Min_Spend($9,$4,$1); MAX_SPEND($12,$8,$3)

Ramya
  • 51
  • 2
  • 10

1 Answers1

1

There's an old function named QUANTILE, but you better replace it with Standard SQL compliant syntax:

3 * (RANK() OVER (ORDER BY spend) - 1) / COUNT(*) OVER() AS Q

This assigns a number between 0 to 2 to each group, you can easily wrap it in a Derived Table and add aggregation on top of it.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Dnoeth, This works perfectly. This split the customer group and assigned ranks. I loaded this on to a volatile table and performed the necessary aggregations. Thank you very much. Not just this, all your answers on teradata has been really helpful. – Ramya Jan 23 '17 at 22:39
  • I have another situation here. Basically, I need to rank the customers under each of the 3 product group, in to high spend custs, low and medium spend custs. I did it all at once, using group by and case statements. But when I pulled the min and the max Sales(Ranking by sales- should be product specific)values for each rank, the middle tier min & max values were the same across all product groups, but I want them all to be ranked independently. Any information on this is appreciated – Ramya Jan 31 '17 at 16:08
  • 1
    @user1806980: You probably need to add `PARTITION BY product_group` to both `OVER` – dnoeth Jan 31 '17 at 16:31
  • PERFECT. I didn't add partition by in the second OVER and that created the issue. It works for me now.Thank you very much. I really appreciate it. – Ramya Feb 20 '17 at 17:25