I had an interesting challenge this week. I needed to get average sales by product but the boss wants to eliminate the top 10% and bottom 10% so she can get a better picture of the actual sales values. We have some customers who buy very few of a product or very many of a product, but most order standard lots. So I learned about the NTILE() function in sql.
-- create temp table of sales by a specific product.
select sales
into #temp0
from table_name
where product_code = 'B149001'
order by sales
-- create ntiles of min and max sales by 10% quartiles. #temp1
-- ntile(10) can be any value like ntile(4).
select max(sales) as [max_sales], min(sales) as [min_sales], quartile
into #temp1
from (select sales, ntile(10) over (order by sales) as [quartile]
from #temp0
) i
group by quartile
-- get the average of the middle 80% of records by eliminating values
-- in the bottom 10% and top 10%.
select avg(sales) as avg_sales from #temp0
where sales > (select max_sales from #temp1 where quartile = 1)
and sales < (select min_sales from #temp1 where quartile = 10)
