Problem

Given a table of data with two columns, "id" and "value", write a SQL query to find all rows where the value is greater than the average value.
Example input:
id | value
1  | 5
2  | 6
3  | 7
4  | 8
5  | 9
Example output:
id | value
3  | 7
4  | 8
5  | 9

Solution

SELECT id, value
FROM table
WHERE value > (SELECT AVG(value) FROM table)

/*

The above solution is optimal because it only requires one pass through the table.

*/