x

Customer Ranking

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’
more ▼

asked Aug 03, 2012 at 08:28 PM in Default

Stuart_Sydney gravatar image

Stuart_Sydney
10 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
SELECT * 
FROM (SELECT CustomerName, FirstOrderDate, NTILE(10) OVER (ORDER BY FirstOrderDate, CustomerID) [Range]
      FROM [dbo].[Customers])d
WHERE [Range] = 2 -- for 11-20% range
more ▼

answered Aug 04, 2012 at 08:38 PM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

THanks Scot ... will give it a try and get back with the results
Aug 06, 2012 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, 2012 at 08:14 PM Stuart_Sydney
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 21, 2012 at 04:12 AM

Scot Hauder gravatar image

Scot Hauder
6.1k 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, 2012 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, 2012 at 09:36 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 27, 2012 at 01:35 AM

Stuart_Sydney gravatar image

Stuart_Sydney
10 1 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, 2012 at 07:27 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x11

asked: Aug 03, 2012 at 08:28 PM

Seen: 736 times

Last Updated: Aug 27, 2012 at 07:27 AM