Finding top 25 % customers for each group using T-SQL

Question

I have a following temp table Customer

   Customer      Group     Price
     A           Sales     100
     B           Lease     200
     C           Lease     300
     D           Lease     50
     E           Lease     100
     F           Sales     750
     G           Sales     200
     H           Lease     50
     I           Sales     130
     J           Lease     100
     K           Lease     200
     L           Sales     500
     M           Sales     1000
     N           Sales     10
     O           Sales     100

I want to find top 25 % customers for each Group.

For eq: Sales has total of 8 customers, so top 25 % would be 2. Hence i need top 2 customers for Sales who have highest Price.

Similarly, for Lease, i have total of 7 customers, top 25 % would be 1.75 which is ~2.

If there is a Customer, which has same Price, Customer which is higher in sorting can be chosen. For eq: Customer B and K have same Price of 200, hence B should be selected.

Here is the desired output:

  Customer      Group     Price
     B           Lease     200
     C           Lease     300
     F           Sales     750
     M           Sales     1000

Thanks all.

score 3 · Accepted Answer · answered Mar 11 '20 at 21:51

In SQL Server 2008, you can use:

select t.*
from (select t.*,
             row_number() over (partition by group order by price desc) as seqnum,
             count(*) over (partition by group) as cnt
      from t
     ) t
where seqnum <= 0.25 * cnt;

Or, use apply:

select t.*
from (select distinct group from t) g cross apply
     (select top (25) percent t.*
      from t
      order by price desc
     ) t

GMB · Answer 2 · 2020-03-11T21:54:24.057

You can use window functions:

select customer, grp, price
from (
    select t.*, percent_rank() over(partition by grp order by price desc, customer desc) prn
    from mytable t
) t
where prn < 0.25
order by grp, price

Demo on DB Fiddle:

customer | grp   | price
:------- | :---- | ----:
K        | Lease |   200
C        | Lease |   300
F        | Sales |   750
M        | Sales |  1000

Edit:

I am usunre that percent_rank() is available in SQL Server 2008, that you tagged your question with (as far as concerned it was introduced in version 2012). We can emulate it as follows:

select customer, grp, price
from (
    select 
        t.*, 
        1.0 
            * rank() over(partition by grp order by price desc, customer desc) 
            / count(*) over(partition by grp) prn
    from mytable t
) t
where prn < 0.25
order by grp, price

Finding top 25 % customers for each group using T-SQL

2 Answers2