# Label data according to percentile

I have a table name temp with column customer_id , sum and I need to add extra column customer_label (may be on view) which is 1 if customer lies in top 10% according to value of sum, 2 if lies within 10%-20%, 3 if lies within 20%-60%, 4 if lies within 60-100%. How can I do this?

Some sample data is included at sqlfiddle

• sqlfiddle.com/#!2/9e8e0/5/1 May 25, 2013 at 8:05
• So, for 50 customers, the Top 5 should have label=1, the next 5 label=2, the next 20 lable=3, etc.? May 25, 2013 at 8:16
• @ypercube yap... but this 50 customer is just for test case... Data row may vary... May 25, 2013 at 13:35
• @ypercube could you help me to write the query? May 25, 2013 at 16:24

select customer_id,sum,case
when pct_row<=0.10 then 1
when pct_row>0.10 and pct_row<=0.20 then 2
when pct_row>0.20 and pct_row<=0.60 then 3
when pct_row>0.60 then 4
end as customer_label from (
select customer_id,sum,(@curRow := @curRow+1)/c as pct_row
from temp t
jOIN (SELECT @curRow := 0) r
JOIN (SELECT @curRow2 := 0) r2
join (select count(*) c from temp) s
order by sum desc) p;


sqlfiddle :- here

I don't know whether this is efficient method or not but work fine for small data set.

WITH CTE (CUSTOMER_ID,SUM,ROW_NUMBERS,CUSTOMER_LABER )
AS
(
SELECT TOP(10) PERCENT WITH TIES A.customer_id,A.SUM ,
ROW_NUMBER()OVER (ORDER BY CUSTOMER_ID) ROW_NUMBERS ,
CASE WHEN CUSTOMER_ID BETWEEN 0 AND 4 THEN '1' ELSE NULL END CUSTOMER_LABER
FROM PERCENTS A
ORDER BY A.CUSTOMER_ID
),
CTE2 (CUSTOMER_ID,SUM,ROW_NUMBERS,CUSTOMER_LABER )
AS
(

SELECT TOP(20) PERCENT WITH TIES A.customer_id,A.SUM ,
ROW_NUMBER()OVER (ORDER BY CUSTOMER_ID) ROW_NUMBERS ,
CASE WHEN CUSTOMER_ID BETWEEN 6 AND 10 THEN '2' ELSE NULL END CUSTOMER_LABER
FROM PERCENTS A
ORDER BY A.CUSTOMER_ID
),
CTE3 (CUSTOMER_ID,SUM,ROW_NUMBERS,CUSTOMER_LABER )
AS
(
SELECT TOP(60) PERCENT WITH TIES A.customer_id,A.SUM ,
ROW_NUMBER()OVER (ORDER BY CUSTOMER_ID) ROW_NUMBERS ,
CASE WHEN CUSTOMER_ID BETWEEN 11 AND 30 THEN '3' ELSE NULL END CUSTOMER_LABER
FROM PERCENTS A
ORDER BY A.CUSTOMER_ID
),

CTE4 (CUSTOMER_ID,SUM,ROW_NUMBERS,CUSTOMER_LABER )
AS(
SELECT TOP(100) PERCENT WITH TIES A.customer_id,A.SUM ,
ROW_NUMBER()OVER (ORDER BY CUSTOMER_ID) ROW_NUMBERS ,
CASE WHEN CUSTOMER_ID BETWEEN 30  AND 50 THEN '4' ELSE NULL END CUSTOMER_LABER
FROM PERCENTS A
ORDER BY A.CUSTOMER_ID
)
SELECT * FROM CTE WHERE CUSTOMER_LABER IS NOT NULL
UNION ALL
SELECT * FROM CTE2 WHERE CUSTOMER_LABER IS NOT NULL
UNION ALL
SELECT * FROM CTE3 WHERE CUSTOMER_LABER IS NOT NULL
UNION ALL
SELECT * FROM CTE4 WHERE CUSTOMER_LABER IS NOT NULL


• MySQL does not have TOP. No CTEs either. No ROW_NUMBER() function either. May 25, 2013 at 13:36
• Ohh sorry I have not seen that your question is from MySql May 25, 2013 at 14:57
• see the Tag ... May 25, 2013 at 15:33