HOw to get top 10% of the customers

Database
Enthusiast

HOw to get top 10% of the customers

Hi,

my problem is that i want to fetch top 10% of the customers in the year 2011 as per there sales in $.

PLease help me with the query..!!!

3 REPLIES
Enthusiast

Re: HOw to get top 10% of the customers


hi, I am using this querry,


select * from SALES_TRANSACTION

where Initial_Channel_Cd ='eCommerce'

and Sales_Tran_Dt between '2011-01-01' and '2011-12-31'

and Concept_Cd='PT'

order by Order_Total_Amt desc;

group by Party_Id

qualify (rank(Order_Total_Amt)) = 1

would it fetch me the result.....? 

total rows are 824339

I want top 10 % of the party-ids whose Order_Total_Amt is largest????


Teradata Employee

Re: HOw to get top 10% of the customers

Something like this - didn't check the syntax, but just to give you an idea to follow:

 

select *, quantile(100, party_id_order_total_amt DESC)

from (

     select a.*, sum(Order_Total_Amt) over (partition by party_id) as party_id_order_total_amt

        from SALES_TRANSACTION as a

     where Initial_Channel_Cd ='eCommerce'

         and Sales_Tran_Dt between '2011-01-01' and '2011-12-31'

         and Concept_Cd='PT'

) t

qualify quantile (100, party_id_order_total_amt) <=10
Enthusiast

Re: HOw to get top 10% of the customers

tnx vburnist..... i guess dat wud do...!!