devxlogo

How to find the top percentage of users

How to find the top percentage of users

Question:
I want to find out the top 5%, 10%, and 30%
customers based on their purchases. For example,
my table looks like:CustomerID Purchase(in $)
1 100
2 500
Up to 100 customers. I am getting the top 5% through:
select top 5 percent Purchase,CustomerID
from Customer
order by CustomerID desc

The same is true for the top 10%. I am getting 10 records
(assuming I have a total of 100 records).But I don’t want to see the first 5 customers,
whom I have already seen as a result of the previous
query. Is there any way I can write code
in order to see only next five records?

Answer:
The strategy is to cull from the data the records you
already saw (I stuck them in a temp table called #keeptrack)
and eliminate subsequent reads by using NOT IN the
next time around like this:

if exists (select * from sysobjects where type = "U" and name like
"#keeptrack")begin drop table #keeptrackendgoset rowcount 5--create table #keeptrack(au_lname varchar(40),
title varchar(80))goinsert into #keeptrack select top 5 percent au_lname
, title from titleview--declare @reccount intselect au_lname, title from titleview where title
not in (select title from #keeptrack) order by au_lname

See also  Redefining Energy Management: Advanced Solutions for Modern Buildings
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist