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.<\/p>\n
-- create ntiles of min and max sales by 10% quartiles. #temp1 -- get the average of the middle 80% of records by eliminating values 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 … Continue reading
\n-- create temp table of sales by a specific product.
\nselect sales
\ninto #temp0
\nfrom table_name
\nwhere product_code = 'B149001'
\norder by sales<\/p>\n
\n-- ntile(10) can be any value like ntile(4).
\nselect max(sales) as [max_sales], min(sales) as [min_sales], quartile
\ninto #temp1
\nfrom (select sales, ntile(10) over (order by sales) as [quartile]
\n from #temp0
\n) i
\ngroup by quartile<\/p>\n
\n-- in the bottom 10% and top 10%.
\nselect avg(sales) as avg_sales from #temp0
\nwhere sales > (select max_sales from #temp1 where quartile = 1)
\nand sales < (select min_sales from #temp1 where quartile = 10)
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"