How do I filter the top 1% and lower 1% of data in each group in SQL

Question

I have a data set that includes PRICE, SUBTYPE, and others. I want to do some outlier removal before I use the dataset. I want to remove rows for things where the price is ridiculously high or low, in each SUBTYPE.

For each SUBTYPE look at the range of the PRICEs and remove or filter out rows. Keep rows that fall between: PRICErange * .01 |KEEP| PRICErange * .99

This was provided to me by a Martin Smith on stackoverflow, I edited this question, so lets start from here.

;WITH CTE       
AS (SELECT *,                   
ROW_NUMBER() OVER (PARTITION BY SUBTYPE ORDER BY PRICE) AS RN,                    
COUNT(*) OVER(PARTITION BY SUBTYPE) AS Cnt             
FROM    all_resale)    
SELECT *    
FROM   CTE    
WHERE (CASE WHEN Cnt > 1 THEN 100.0 * (RN -1)/(Cnt -1) END) BETWEEN 1 AND 99

I'm not sure this is what I need to do. I don't know how many rows will be removed off the ends.

Answer 1

You don't specify exactly how you define the 1 percent and how ties should be handled.

One way is below

;WITH CTE
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY SUBTYPE ORDER BY PRICE) AS RN,
                COUNT(*) OVER(PARTITION BY SUBTYPE) AS Cnt
         FROM    all_resale)
SELECT *
FROM   CTE
WHERE (CASE WHEN Cnt > 1 THEN 100.0 * (RN -1)/(Cnt -1) END) BETWEEN 1 AND 99

That assumes the highest price item is 100%, the lowest price one 0% and all others scaled evenly between taking no account of ties. If you need to take account of ties look into RANK rather than ROW_NUMBER

NB: If all of the subtypes have a relatively large amount of rows you could use NTILE(100) instead but it does not distribute between buckets well if the number of rows is small relative to number of buckets.