SQL Interview Questions – Averages without High or Low Values.

Calculate the average value without the highest or lowest value in a column. In statistics this is also called Trimmed mean which reduce the effect of outliers in a data set.

Solution –

To solve this problem, we can use a subquery to exclude the Min and Max values.

SELECT
    AVG(Total) avg_order_value
FROM
    invoice
WHERE
    Total NOT IN (
        (SELECT MAX(Total) FROM invoice),
        (SELECT MIN(Total) FROM invoice)
    );

First we find the minimum and maximum value of the total column inside the inner query. Then we use a WHERE Total NOT IN to exclude these min and max values in the outer query before calculating the averages.