Customer Ranking

Question

I have 10,000 customers and have been trying (for some time) to calculate a ranking based on how long each customer has been with the business.

Calculating the top 10% is easy but does anyone have any idea how to calculate those customers that are between 11% - 20%, 21% -30% and so on?

Stu

PS … I need to be able to do this ‘on the fly’

Answer 1

0

SELECT * 
FROM (SELECT CustomerName, FirstOrderDate, NTILE(10) OVER (ORDER BY FirstOrderDate, CustomerID) [Range]
      FROM [dbo].[Customers])d
WHERE [Range] = 2 -- for 11-20% range

answered Aug 04 '12 at 08:38 PM

Scot Hauder
5.7k ● 13 ● 15 ● 18

THanks Scot ... will give it a try and get back with the results

Aug 06 '12 at 04:40 AM Stuart_Sydney

Scot ... Getting there, just working out how to set the parameters for each range.

THanks for pointing in the right direction.

Stu

Aug 07 '12 at 08:14 PM Stuart_Sydney

(comments are locked)

Answer 2

0

If your group percentages are set try this:

SELECT CustomerName,
CASE WHEN [Range] = 1 THEN 'Group1' -- 1-5%
     WHEN [Range] = 2 THEN 'Group2' -- 6-10%
     WHEN [Range] BETWEEN 3 AND 4 THEN 'Group3'  --11-20%
     WHEN [Range] BETWEEN 5 AND 10 THEN 'Group4' --21-50%
     ELSE 'Group5' END
FROM (SELECT CustomerName, FirstOrderDate, NTILE(20) OVER (ORDER BY FirstOrderDate, CustomerID) [Range]
      FROM [dbo].[Customers])d

answered Aug 21 '12 at 04:12 AM

Scot Hauder
5.7k ● 13 ● 15 ● 18

Scot

NTILE will not work in this case as NTILE divides the rowset into equal groups.

I need to divide the rowset into 5 unequal groups for example:

group 1 = top 5%, group 2 = 6 to 10%, group 3 = 11 to 20%, group 4 = 21 to 50%, group 5 = whatever is left (its an 80/20 Pareto principle thing where the top 20% yield 80% of revenue)

The only way forward I can see is to build a custom function

Any thoughts?

Thanks

Stu

Aug 21 '12 at 02:22 AM Stuart_Sydney

@Stuart_sidney - Scot solution here does that by identifying the common denominator for the groups you want you then partition the records using the CASE test on the value that NTile in the inner SELECT has provided. You could make this cover any range by using NTILE(100) and then accounting for every value of [range] in the CASE.

Aug 21 '12 at 09:36 AM Fatherjack ♦♦

(comments are locked)

Answer 3

0

Scot / Fatherjack, Thanks for the samples ... in the end I took somewhat of a shortcut.

I created a query on the customer file using RANK. Then wrote a module to allow the user to set whatever levels they want and run a table update.

Thanks for the help.

answered Aug 27 '12 at 01:35 AM

Stuart_Sydney
10 ● 1 ● 2

That sounds like a risky solution. If you persist the levels into the table, it will be invalid/incorrect as soon as new customers arrive.

Aug 27 '12 at 07:27 AM Magnus Ahlkvist

(comments are locked)

Customer Ranking

Follow this question

Related Questions