sql - How do I exclude top and bottom 5% of annual salary on 18 million records in TSQL, then use the seleced data to calculate average -

i have 18 million rows in column (income), , exclude top & bottom 5% of income calculate more accurate average on income.

you didn't provide data structure, grouping , on. proof of concept. calculate percent_rank(), narrow data, calculate average.

sqlfiddledemo

/* preparing data */ create table tab(id int identity(1,1), income int)  ;with nums(number) (select 1 number   union  select number+1 nums number<100   /* warn here recursive cte */ ) insert tab(income) select number nums;   /* main query */ cte(id, income, [percent]) (   select         id       ,income       ,[percent] = percent_rank() over(order income)   tab ) select [average_income] =  avg(income) cte     [percent] > 0.05     , [percent] < 0.95